•  
  • Archives for July 2011 (4)

SQL Server Denali CTP3 Download

by Muthukkumaran kaliyamoorthy Published on: July 21, 2011
Comments: No Comments
Tags:
Categories:Microsoft

Microsoft has given the SQL Server Denali CTP3 (Community Technology Preview)  for public preview. You can download the SQL Server Denali CTP3 here.

 

http://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx

I’m very interested to see the SQL Server Denali new Features.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

SQL Server 2008 SP2 cumulative update 5

by Muthukkumaran kaliyamoorthy Published on: July 20, 2011
Comments: No Comments
Tags: ,
Categories:Microsoft

SQL Server 2008 SP2 10.50.2418.0 latest cumulative update 5 available now. You may download and test it.

Cumulative Update 5 for SQL Server 2008 Service Pack 2

http://support.microsoft.com/kb/2555408

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

T-SQL best practice SQL server

by Muthukkumaran kaliyamoorthy Published on: July 12, 2011
Comments: 13 Comments
Tags: ,
Categories:Performance, SQL party

SQL server T-SQL best practice

This month’s TSQL Tuesday party is being hosted by Amit Banerjee (Blog | Twitter).

He is working with Microsoft and see here his online activities MSDN. The topic this month is T-SQL best practice. It’s the both DBA and developers Topic/Job.

Transact SQL is the best tool to improve the SQL server overall performance. You write your code efficiently then you no need to spend lots of money to buy extra resources like hardware, RAM etc… So the coders/developers have to know the best practice.

He or She does a good job (writing correct codes) then, There is no work for the DBAs. There is lots of T-SQL best practice available on the Internet. Here I’m going to share mine. The first 15 bullet points are personally I have faced and tuned in my environment. I will have planned to write each post for all 15 points with examples.

T-SQL best practice

DOs and Don’Ts

  • Check/Validate your conditions out of the loop (While/If) statement.
  • Specify exact data type size

Ask the customer and specify the correct data type size what they really need.  Otherwise you will end up with a problem when your data grow larger.

E.X For inserting a 4 digit character don’t create CHAR (400) just create CHAR (4)

  • Always write a query using seek-able operator in your WHERE clause.

http://msdn.microsoft.com/en-us/library/ms172984.aspx

  • Try to avoid Functions and date time functions when you write Quires i.e. Sproc .
  • Avoid an index hint

Use the index hint if you really know it’ll improve the performance otherwise avoid it.

SELECT * FROM emp  WITH (INDEX (ix_n)) WHERE n ='server'
  • Don’t create too many indexes.

Create the indexes it’s really useful otherwise don’t. I have seen a table has 5 non clustered indexes with mostly same column definitions for a particular table/SP and the table size is 4 GB and the index size are nearly 9 GB.

  • Don’t use wildcard characters at the beginning of the word
  • Always write seekable codes.
SELECT ID FROM <table_name> WHERE NAME LIKE '%kumar'

-- use below

SELECT ID FROM <table_name> WHERE NAME LIKE 'muthu%'

 

  • Use “order by” and “distinct” – sorting, really when there is a need.

If you have a doubt on that, you can test run the code with/without sorting, you come to know, how it perform well.

  • Normalize your tables

The best design will give the best performance. Normalize your tables before going to your project. I.e. my second point (Specify correct data type size etc…)

  • Try to write set based queries minimize iteration/Cursors as much as you can.
  • Use joins instead of sub query

Use the joins instead of sub query it’ll give better performance.

 

SELECT * FROM TBL WHERE N IN (SELECT N FROM TBL1)

-- use below

SELECT A. * FROM TBL A JOIN TBL1 B
ON (A. N=B. N)

 

  • Use the batch statement if you’re going to delete a huge number of records from the table.

Write a batch statement if you’re going to delete huge records it’ll minimize the log file size. Most importantly inform to the DBA team before going to delete otherwise we will get a call from a customer the DB was down 🙂

  • Use ANSI-Standard Join clauses instead of the old style joins.

 

SELECT e.no,ed.name
FROM employee e, employee_details ed,
WHERE ed.name ='muthu'

-- use below

SELECT e.no,ed.name
FROM employee e INNER JOIN employee_details ed
ON emp.no=ed.no
WHERE ed.name ='muthu'

 

  • Test your data and the indexes after migrating one server to another server especially 2000  to 2005/2008.
  • Create a filtered index if you know the query often fetch the unchanged data from a big table.

A well-designed filtered index can improve query performance, reduce storage costs, and reduce maintenance too.

 

CREATE NONCLUSTERED INDEX I_fi_test
ON dbo.test(ID,joinDate,Relievingdate)
WHERE joinDate > '2001/01/01';
  • Use NO LOCK hint if you have no problem with dirty reads.

See my friend’s article http://sqlvibes.blogspot.in/2011/07/beware-of-nolock.html

  • Create a clustered index (Primary key) for all mostly used tables

Try to create a clustered index for all the tables. I had seen most of my big tables are not defragmented because of the tables are created primary key with NON clustered index. I asked the reason they said we don’t know who has created.

Use SELECT 1 instead of SELECT *

Use SELECT 1 when you’re checking the records are available at your table.

IF EXISTS (SELECT 1 FROM <table_name> WHERE id=100)
  • In general rule retrieve the columns as you want don’t use “SELECT *”
  • Write a required column in the SELECT statement as you want don’t SELECT all the columns.

E.X

SELECT * FROM <table_name>

-- use below

SELECT c1,c2 FROM <table_name>
  • Don’t write your procedure name starting with “SP_”

SQL server searches all the producers one by one starting with “SP_” in the database including system procedure then finally found yours. It causes minor overhead.

  • Use WHERE condition as much as possible
SELECT * FROM emp WHERE Name ='SERVER'
  • Use TRY-Catch for error handling

See my procedure has written using Try-Catch.

  • Try to avoid dynamic SQL

It’ll generate SQL injections if you’re not writing it effectively.

http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

  • Always write your codes/SProcs with descriptions and comments.

I hope this best practice will help for the developers/coders. Thanks to Amit has given this great topic.

Edit: Drop me a comment or email if you have / find any t-SQL best practice I will add that too.

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

SQL server Database backup restores Steps

by Muthukkumaran kaliyamoorthy Published on: July 6, 2011
Comments: 15 Comments
Tags: ,
Categories:Backup/Restore, DBA

SQL server Database restores sequence Steps

I have seen many people are still unclear about the database restoration sequence. Recently I have answered a number of database restoration questions SSC, MSDN… I got a comment from Anonymous user.

It’s a simple task to the experienced DBAs. However it’s very important to know the database restoration sequence. It’ll help number of situations.

For example,

Your database got damaged in any one of the reasons. (Or) You may get a request from the DEV team to restore a database from production to development.

I have already written an article about Step by step backup/restore using T-SQL. But it’s not fully focus the restoration sequence.

Here I am going to give you the scripts with explanation and examples too. I hope this will help you.

I have scheduled every Sunday full backup @1 AM (1 AM) and every @6 hour differential backup (daily 2 AM onwards) and every @3 hour log backup (2:30 Am onwards).

Just for giving real time example I have moved the log files from current drive (C) to another one drive (D) and i started the SQL server. I have seen the database that shows without the expandable symbol (i.e. +).

Now the database “IMPORTANT_DB” log file  has gone on 13 th jun 2011 Monday at 11:40 PM

Now How can I bring the database back to online?

As I already know about my backup policy even though I supposed to check the system tables because where the backup is located and the backup job got succeeded or not.

 

Here is the script to check the backup details.

SELECT
database_name,[type],backup_finish_date,

(backup_size/1024/1024) size_mb,physical_device_name,

[user_name]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(a.media_set_id=b.media_set_id) WHERE

DATABASE_NAME in('dbadata')

--type ='d'and

--and backup_finish_date>=getdate()-14

ORDER BY backup_finish_date DESC

 

It’s very important to take the tail log backup before going to do anything. Lets try the tail log backup.

BACKUP LOG [dbadata] TO  DISK = N'C:\Backup\dbadata_Taillog.trn'WITH  NO_TRUNCATE

 

Msg 3447, Level 16, State 1, Line 1

Could not activate or scan all of the log files for database ‘dbadata’.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

 

In my case, I can’t do the tail log backup.

Check the database if it is online or not by using below command.

 

SELECT state_desc,DB_NAME(database_id) FROMSYS.MASTER_FILESWHERE state_desc  <>'ONLINE'

 

Let me try to access the database

USE DBADATA

 

Got error I can’t access the database
Msg 945, Level 14, State 2, Line 1

Database’dbadata’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQLServer errorlog for details.
Let’s check the error log

EXEC SP_READERRORLOG

Error: 17207, Severity: 16, State: 1.

FileMgr::StartLogFiles: Operating system error 2(failed to retrieve textfor this error. Reason: 15100) occurred while creating or opening file’XXX \dbadata.ldf’. Diagnose and correct the operating system error, and retry the operation.
Fileactivation failure. The physical file name “XXX dbadata.ldf” may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, nocheckpoint occurred to the database, or the database was read-only. This error could occur if the transactionlogfile was manually deleted or lost due to a hardware or environment failure.

OK now run the following script and find the backup location.

SELECT
database_name,[type],backup_finish_date,

(backup_size/1024/1024) size_mb,physical_device_name,

[user_name]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(a.media_set_id=b.media_set_id) WHERE

DATABASE_NAME in('dbadata')

--type ='d'and

--and backup_finish_date>=getdate()-14

ORDER BY backup_finish_date DESC

 

 

Backup details of the scripts for that particular DB

Backup Device
Type
Y:\Backup\dbadata_full_12_jun_2011_1AM.bak
Full
Y:\Backup\dbadata_Diff_13_jun_2011_2AM.bak
Differential
Y:\Backup\dbadata_Diff_13_jun_2011_8AM.bak  
Y:\Backup\dbadata_Diff_13_jun_2011_2PM.bak
Differential
Differential
Y:\Backup\dbadata_Diff_13_jun_2011_8PM.bak
Differential
Y:\Backup\dbadata_log_13_jun_2011_230AM.trn
Log
Y:\Backup\dbadata_log_13_jun_2011_530AM.trn
Log
Y:\Backup\dbadata_log_13_jun_2011_830AM.trn
Log
Y:\Backup\dbadata_log_13_jun_2011_1130AM.trn
Log
Y:\Backup\dbadata_log_13_jun_2011_230PM.trn
Log
Y:\Backup\dbadata_log_13_jun_2011_530PM.trn
Log
Y:\Backup\dbadata_log_13_jun_2011_830PM.trn
Log
Y:\Backup\dbadata_log_13_jun_2011_1130PM.trn
Log


Steps to restore the database using available backups.

RESTORE FILELISTONLY FROM DISK ='Y:\Backup\dbadata_full_12_jun_2011_1AM.bak'

First put the database in single user mode by using the below command.

ALTER DATABASE DBAdata
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Here I am going to overwrite (replace) the database. If you want you can leave the damaged database and restore a new one.

Restore the latest full database backup has done on Sunday with norecovery option.

RESTORE DATABASE DBAdata FROM DISK ='Y:\Backup\dbadata_full_12_jun_2011_1AM.bak'
WITH replace,norecovery

 

Next run the latest differential backup done on Monday at 8 PM with norecovery option.

RESTOREDATABASE DBAdata FROMDISK ='Y:\Backup\dbadata_Diff_13_jun_2011_8PM.bak'
WITHnorecovery

 

Then lastly run the log backup one by one done on Monday after the differential backup.

 

RESTORE DATABASE DBAdata FROM DISK ='Y:\Backup\dbadata_log_13_jun_2011_830PM.trn'
with norecovery
RESTORE DATABASE DBAdata FROM DISK ='Y:\Backup\dbadata_log_13_jun_2011_1130PM.trn'
WITH recovery

 

If you missed any one of the previous log backups then you will get this error. That’s why people are recommended to keep all log backups safely.

Msg 4305, Level 16, State 1, Line 2

The log in this backupset begins at LSN 3873000000044400001, which is too recent to apply to the database. An earlier logbackup that includes LSN 3873000000042400001 can be restored.

Msg 3013, Level 16, State 1, Line 2

RESTOREDATABASE is terminating abnormally.

What was given just an example. Hope this post clears the backup restoration sequence.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

page 1 of 1

Welcome , today is Tuesday, May 23, 2017