SQL Profiler is a wonderful tool provided along with SQL Server Setup to monitor activities running on a SQL Server at any point of time. However people might wonder I get the same when I run sp_who or sp_who2 and what extra one gets by using profiler. Any other monitoring tool would provide process or connection level information and we have to use multiple steps to locate what activity is performed by a particular process and that is where SQL Profiler is handy.
Now let us know see how to use SQL Profiler and when to SQL Profiler
Start Menu-> Run and type profiler, in the UI provide server/User and password and then choose default trace and click on run and we could commands which are fired against the server.
The basic information we receive from profiler are TextData, Application Name, LoginName, Reads, Writes, CPU, Duration, SPId, StartTime, EndTime of which main parameter is TextData which shows the data sent from the client to the server along with the command executed.
Say for example I execute say a procedure Proc_test with Parameter1 value as "Test" and Parameter2 value as 120 then the TextData would show value as
EXEC Proc_Test "Test",120
This is very useful for troubleshooting as many of times we end up with application users saying its a query or procedure bug and we find finally that application has passed a hard coded value as the application developer forgot to change the code or parameter values were jumbled. So first thing any database developer needs to do when a bug or issue is logged as saying no rows returned or wrong data fetched from procedure would be to run profiler and confirm correct values are passed to database before analyzing the issue which would save lots of human errors and speedy recovery of issues.
There are other useful information which are available which help us troubleshoot from performance perspective
Reads - How much of reads is performed for each statements which would help us determine how much data is read from disk instead of buffer
Writes - How much of Writes is performed for each statement from which we can again know how much of IO is being consumed
CPU - CPU is the amount of CPU time the query consumed
Duration - Duration is the amount of time it took to execute the statement.
Needless to say that duration is the first parameter to see and locate statements which has more duration and then take the statement from Textdata and analyze further on whether reads and writes for those statements are appropriate or tuning is required from Index perspective or re-write query logic. To get statement level we need to use SQLBatchCompleted as RPCCompleted would just return procedure level and not statement level.
And also as a responsible developer we need to understand that SQL Profiler also consumes some amount of CPU on the server as it monitors the server continuously and
so it is restricted to only sysadmins.
Also we need to use SQL Profiler cautiously and not choose this for all troubleshooting issues , so talk to the DBA when in real need and DBAs can give permission to user for
running the same.
One more good option we get from profiler would be save these traces as a trace file or to SQL Server table so we can analyze these traces in future for troubleshooting.
In the coming weeks we would see some more features on the SQL Profiler to leverage this tool in a still better way to solve many of our issues…………
Tuesday, September 7, 2010
Dynamic Statements - sp_executesql Vs EXEC
Dynamic Statements are ineffective to use as SQL Server would not be able to determine a execution during compilation time and so SQL Server would create the execution plan
each and every time when such statements are executed.
The greatest advantage of Stored Procedure apart from reusability is that the code is compiled and so SQL would save some execution time by not invoking compiler for creating
creating execution plan. What would happen if we need to use dynamic statements in our procedure due to a business need would the same be compiled once and then cached or does it each time create execution plan ?
Answer would be it depends on how we invoke dynamic statements. Generally there are 2 ways of invoking dynamic statements one to EXEC statement and the next one would be
to use sp_executesql . Of which sp_executesql is the one which needs to be used as the plan is cached for the same.
As always let us see an example to understand this better:
IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_SYS_CACHE')
DROP TABLE TEST_SYS_CACHE
GO
CREATE TABLE TEST_SYS_CACHE
( Col1_Int INT IDENTITY(1,1) NOT NULL,
Col2_Varchar VARCHAR(255) NOT NULL,
Col3_Varchar VARCHAR(2000) NOT NULL,
COl4_Flag BIT NOT NULL
)
--Poplulate 1000 rows
DECLARE @iCount INTEGER
SELECT @iCount = 1
WHILE @iCount < = 1000
BEGIN
INSERT INTO TEST_SYS_CACHE(Col2_Varchar,Col3_Varchar,COl4_Flag)
SELECT 'ABC' + CONVERT(VARCHAR,@iCount),'\\ABC' + CONVERT(VARCHAR,@iCount) + '\Share',1
SELECT @iCount = @iCount + 1
END
DBCC FREEPROCCACHE
--Execute this query multiple times and you would see usecount is always 1
DECLARE @Col_Value_E INT
DECLARE @Qry_Cache_E VARCHAR(7000)
SELECT @Col_Value_E = 100
SELECT @Qry_Cache_E = 'SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = ' + CONVERT(VARCHAR,@Col_Value_E)
EXEC (@Qry_Cache_E)
SELECT sql,usecounts,* from sys.syscacheobjects WHERE objtype = 'Prepared'
DBCC FREEPROCCACHE
--Execute this query multiple times and you would see usecount keeps increasing
DECLARE @Col_Value INT
DECLARE @Qry_Cache NVARCHAR(4000)
SELECT @Col_Value = 100
SELECT @Qry_Cache = 'SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = @Col_Value'
EXEC sp_executesql @Qry_Cache, N'@Col_Value INT', @Col_Value = @Col_Value
SELECT sql,usecounts,* from sys.syscacheobjects WHERE objtype = 'Prepared'
/* Output
sql usecounts
(@1 tinyint)SELECT * FROM [TEST_SYS_CACHE] WHERE [Col1_Int]=@1 1
sql usecounts
(@Col_Value INT)SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = @Col_Value 4
/* Output
So from the above we can infer that sp_executesql plan is cached and used for subsequent runs but for EXEC each time the plan is recreated
each and every time when such statements are executed.
The greatest advantage of Stored Procedure apart from reusability is that the code is compiled and so SQL would save some execution time by not invoking compiler for creating
creating execution plan. What would happen if we need to use dynamic statements in our procedure due to a business need would the same be compiled once and then cached or does it each time create execution plan ?
Answer would be it depends on how we invoke dynamic statements. Generally there are 2 ways of invoking dynamic statements one to EXEC statement and the next one would be
to use sp_executesql . Of which sp_executesql is the one which needs to be used as the plan is cached for the same.
As always let us see an example to understand this better:
IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_SYS_CACHE')
DROP TABLE TEST_SYS_CACHE
GO
CREATE TABLE TEST_SYS_CACHE
( Col1_Int INT IDENTITY(1,1) NOT NULL,
Col2_Varchar VARCHAR(255) NOT NULL,
Col3_Varchar VARCHAR(2000) NOT NULL,
COl4_Flag BIT NOT NULL
)
--Poplulate 1000 rows
DECLARE @iCount INTEGER
SELECT @iCount = 1
WHILE @iCount < = 1000
BEGIN
INSERT INTO TEST_SYS_CACHE(Col2_Varchar,Col3_Varchar,COl4_Flag)
SELECT 'ABC' + CONVERT(VARCHAR,@iCount),'\\ABC' + CONVERT(VARCHAR,@iCount) + '\Share',1
SELECT @iCount = @iCount + 1
END
DBCC FREEPROCCACHE
--Execute this query multiple times and you would see usecount is always 1
DECLARE @Col_Value_E INT
DECLARE @Qry_Cache_E VARCHAR(7000)
SELECT @Col_Value_E = 100
SELECT @Qry_Cache_E = 'SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = ' + CONVERT(VARCHAR,@Col_Value_E)
EXEC (@Qry_Cache_E)
SELECT sql,usecounts,* from sys.syscacheobjects WHERE objtype = 'Prepared'
DBCC FREEPROCCACHE
--Execute this query multiple times and you would see usecount keeps increasing
DECLARE @Col_Value INT
DECLARE @Qry_Cache NVARCHAR(4000)
SELECT @Col_Value = 100
SELECT @Qry_Cache = 'SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = @Col_Value'
EXEC sp_executesql @Qry_Cache, N'@Col_Value INT', @Col_Value = @Col_Value
SELECT sql,usecounts,* from sys.syscacheobjects WHERE objtype = 'Prepared'
/* Output
sql usecounts
(@1 tinyint)SELECT * FROM [TEST_SYS_CACHE] WHERE [Col1_Int]=@1 1
sql usecounts
(@Col_Value INT)SELECT * FROM TEST_SYS_CACHE WHERE Col1_Int = @Col_Value 4
/* Output
So from the above we can infer that sp_executesql plan is cached and used for subsequent runs but for EXEC each time the plan is recreated
Tuesday, August 17, 2010
How to remove a specific query plan from cache.
Sometimes our query cache would use the same table structure even if the table structure has been changed say we added or removed more columns , but the query cache still would follow old schema and we receive error that schema doesnt match.
Example:Error Line: 262; Error Severity: 16; Error State: 4;
Error Message: Could not complete cursor operation because the table schema changed after the cursor was declared.
In such cases below query becomes handy to clear the query cache
-- Use the below query to get the handle of the query which is being run
SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM TableA%'
GO
-- Use the SQL Handle to remove the specific plan from the cache
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000)
GO
Example:Error Line: 262; Error Severity: 16; Error State: 4;
Error Message: Could not complete cursor operation because the table schema changed after the cursor was declared.
In such cases below query becomes handy to clear the query cache
-- Use the below query to get the handle of the query which is being run
SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM TableA%'
GO
-- Use the SQL Handle to remove the specific plan from the cache
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000)
GO
Monday, August 9, 2010
Initiate a Backup of database
How do I initiate a backup of database automatically using script and backup file needs to be
unique with date and time(till seconds) is included in backup file.
Below mentioned command takes DBName as the input parameter and it creates a folder based on database name and places backup file inside that.
DECLARE @DBNAME VARCHAR(100)
DECLARE @EXEC_STR VARCHAR(100)
DECLARE @Backup_Folder VARCHAR(5000)
DECLARE @Backup_Path VARCHAR(5000)
SELECT @DBNAME = 'master'
SELECT @Backup_Folder = 'E:\' + @DBNAME + '\Backup\'
SELECT @EXEC_STR = 'md ' + @Backup_Folder
exec master..xp_cmdshell @EXEC_STR
SELECT @Backup_Path = @Backup_Folder + @DBNAME + '_Full_' + replace(replace(convert(varchar(20),getdate(),113),':','_'),' ' , '_') + '.bak'
SELECT @Backup_Path
BACKUP DATABASE @DBNAME TO DISK = @Backup_Path WITH NOFORMAT
, NOINIT
, NAME = 'Full Database Backup'
, SKIP,
,STATS = 5
GO
unique with date and time(till seconds) is included in backup file.
Below mentioned command takes DBName as the input parameter and it creates a folder based on database name and places backup file inside that.
DECLARE @DBNAME VARCHAR(100)
DECLARE @EXEC_STR VARCHAR(100)
DECLARE @Backup_Folder VARCHAR(5000)
DECLARE @Backup_Path VARCHAR(5000)
SELECT @DBNAME = 'master'
SELECT @Backup_Folder = 'E:\' + @DBNAME + '\Backup\'
SELECT @EXEC_STR = 'md ' + @Backup_Folder
exec master..xp_cmdshell @EXEC_STR
SELECT @Backup_Path = @Backup_Folder + @DBNAME + '_Full_' + replace(replace(convert(varchar(20),getdate(),113),':','_'),' ' , '_') + '.bak'
SELECT @Backup_Path
BACKUP DATABASE @DBNAME TO DISK = @Backup_Path WITH NOFORMAT
, NOINIT
, NAME = 'Full Database Backup'
, SKIP,
,STATS = 5
GO
Calculate BACKUP & Restore Time Estimate
Use this DMV to calculate the estimated time for restore and backup operations:
SELECT command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
SELECT command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
Friday, August 6, 2010
What is Selectivity
What is Selectivity:
The ratio between the number of distinct values in the indexed column to the number of records in a table is called as selectivity of an index. So it is obvious that ideal selectivity
would be 1(or Closer to 1) which means most of the values in column are unique
Examples of Good Selectivity:
Consider a table having say 1000 records and one of the indexed column has 900 distinct values then the selectivity would be 900/1000 = 0.90
Indexes on these columns are the most selective and the most effective in optimizing performance. So, the term selectivity of an index means percentage of rows in a table having
the same value for the indexed column.
Examples of Bad Selectivity:
Consider a table having say 1000 records and one of the indexed column has 50 distinct values then the selectivity would be 50/1000 = 0.05
It is evident that a full table scan is more efficient as using such an index where much more I/O is needed to scan repeatedly the index and the table.
B-TREE Indexes improve performance of queries which select small percentage of rows from a table. As a thumb rule, we should create indexes on tables that are often
queried for less than 15% of the table's rows.
Let us understand selectivity by means of a example
IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_INDEX_SEL')
DROP TABLE TEST_INDEX_SEL
GO
CREATE TABLE TEST_INDEX_SEL
( Col1_Int INT IDENTITY(1,1) NOT NULL,
Col2_Varchar VARCHAR(255) NOT NULL,
Col3_Varchar VARCHAR(2000) NOT NULL,
COl4_Flag BIT NOT NULL
)
--Poplulate 1000 rows
DECLARE @iCount INTEGER
SELECT @iCount = 1
WHILE @iCount < = 1000
BEGIN
INSERT INTO TEST_INDEX_SEL(Col2_Varchar,Col3_Varchar,COl4_Flag)
SELECT 'ABC' + CONVERT(VARCHAR,@iCount),'\\ABC' + CONVERT (VARCHAR,@iCount) + '\Share',1
SELECT @iCount = @iCount + 1
END
--Update some rows randomly for sampling
UPDATE TEST_INDEX_SEL
SET Col2_Varchar = 'ABC2',
Col3_Varchar = '\\ABC5\Share',
COl4_Flag = 0
WHERE Col1_Int IN ( SELECT TOP 100 COL1_INT FROM TEST_INDEX_SEL ORDER BY NEWID())
UPDATE TEST_INDEX_SEL
SET Col2_Varchar = 'ABC203',
Col3_Varchar = '\\ABC764\Share',
COl4_Flag = 0
WHERE Col1_Int IN ( SELECT TOP 100 COL1_INT FROM TEST_INDEX_SEL ORDER BY NEWID())
UPDATE TEST_INDEX_SEL
SET Col3_Varchar = '\\ABC378\Share',
COl4_Flag = 0
WHERE Col1_Int IN ( SELECT TOP 100 COL1_INT FROM TEST_INDEX_SEL ORDER BY NEWID())
Now for example selectivity of Col2_Varchar can be calculated as follows:
SELECT COUNT (DISTINCT Col2_Varchar) FROM TEST_INDEX_SEL
-- Output
-- 808
SELECT COUNT ('X') FROM TEST_INDEX_SEL
-- Output
-- 1000
So selectivity would be arrived by dividing these 2 values which is not bad and its 0.8
which is just 20% away from ideal value of 1
SELECT 808/1000 = 0.808
--To find for all columns in that table
SELECT CONVERT(NUMERIC(5,2),COUNT (DISTINCT Col2_Varchar))/
(SELECT COUNT ('X') FROM TEST_INDEX_SEL),
CONVERT(NUMERIC(5,2),COUNT (DISTINCT Col3_Varchar))/
(SELECT COUNT ('X') FROM TEST_INDEX_SEL),
CONVERT(NUMERIC(5,2),COUNT (DISTINCT COl4_Flag))/
(SELECT COUNT ('X') FROM TEST_INDEX_SEL)
FROM TEST_INDEX_SEL T
/* Output
Col2_Selecvity Col3_Selectivity Col4_Selectivity
0.8080000000000 0.7260000000000 0.0020000000000
*/
By the above you we can infer that its ideal to create indexes on Col2 if the same is used in WHERE clauses and it wouldn’t help much to create on Col4 as it has low selectivity and that is the reason why many tables which have a bit value as 0 or 1 tend to be slow as it has low selectivity.
The ratio between the number of distinct values in the indexed column to the number of records in a table is called as selectivity of an index. So it is obvious that ideal selectivity
would be 1(or Closer to 1) which means most of the values in column are unique
Examples of Good Selectivity:
Consider a table having say 1000 records and one of the indexed column has 900 distinct values then the selectivity would be 900/1000 = 0.90
Indexes on these columns are the most selective and the most effective in optimizing performance. So, the term selectivity of an index means percentage of rows in a table having
the same value for the indexed column.
Examples of Bad Selectivity:
Consider a table having say 1000 records and one of the indexed column has 50 distinct values then the selectivity would be 50/1000 = 0.05
It is evident that a full table scan is more efficient as using such an index where much more I/O is needed to scan repeatedly the index and the table.
B-TREE Indexes improve performance of queries which select small percentage of rows from a table. As a thumb rule, we should create indexes on tables that are often
queried for less than 15% of the table's rows.
Let us understand selectivity by means of a example
IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_INDEX_SEL')
DROP TABLE TEST_INDEX_SEL
GO
CREATE TABLE TEST_INDEX_SEL
( Col1_Int INT IDENTITY(1,1) NOT NULL,
Col2_Varchar VARCHAR(255) NOT NULL,
Col3_Varchar VARCHAR(2000) NOT NULL,
COl4_Flag BIT NOT NULL
)
--Poplulate 1000 rows
DECLARE @iCount INTEGER
SELECT @iCount = 1
WHILE @iCount < = 1000
BEGIN
INSERT INTO TEST_INDEX_SEL(Col2_Varchar,Col3_Varchar,COl4_Flag)
SELECT 'ABC' + CONVERT(VARCHAR,@iCount),'\\ABC' + CONVERT (VARCHAR,@iCount) + '\Share',1
SELECT @iCount = @iCount + 1
END
--Update some rows randomly for sampling
UPDATE TEST_INDEX_SEL
SET Col2_Varchar = 'ABC2',
Col3_Varchar = '\\ABC5\Share',
COl4_Flag = 0
WHERE Col1_Int IN ( SELECT TOP 100 COL1_INT FROM TEST_INDEX_SEL ORDER BY NEWID())
UPDATE TEST_INDEX_SEL
SET Col2_Varchar = 'ABC203',
Col3_Varchar = '\\ABC764\Share',
COl4_Flag = 0
WHERE Col1_Int IN ( SELECT TOP 100 COL1_INT FROM TEST_INDEX_SEL ORDER BY NEWID())
UPDATE TEST_INDEX_SEL
SET Col3_Varchar = '\\ABC378\Share',
COl4_Flag = 0
WHERE Col1_Int IN ( SELECT TOP 100 COL1_INT FROM TEST_INDEX_SEL ORDER BY NEWID())
Now for example selectivity of Col2_Varchar can be calculated as follows:
SELECT COUNT (DISTINCT Col2_Varchar) FROM TEST_INDEX_SEL
-- Output
-- 808
SELECT COUNT ('X') FROM TEST_INDEX_SEL
-- Output
-- 1000
So selectivity would be arrived by dividing these 2 values which is not bad and its 0.8
which is just 20% away from ideal value of 1
SELECT 808/1000 = 0.808
--To find for all columns in that table
SELECT CONVERT(NUMERIC(5,2),COUNT (DISTINCT Col2_Varchar))/
(SELECT COUNT ('X') FROM TEST_INDEX_SEL),
CONVERT(NUMERIC(5,2),COUNT (DISTINCT Col3_Varchar))/
(SELECT COUNT ('X') FROM TEST_INDEX_SEL),
CONVERT(NUMERIC(5,2),COUNT (DISTINCT COl4_Flag))/
(SELECT COUNT ('X') FROM TEST_INDEX_SEL)
FROM TEST_INDEX_SEL T
/* Output
Col2_Selecvity Col3_Selectivity Col4_Selectivity
0.8080000000000 0.7260000000000 0.0020000000000
*/
By the above you we can infer that its ideal to create indexes on Col2 if the same is used in WHERE clauses and it wouldn’t help much to create on Col4 as it has low selectivity and that is the reason why many tables which have a bit value as 0 or 1 tend to be slow as it has low selectivity.
Wednesday, August 4, 2010
Query to change default data and log path
This query modifies the registry value of a data and log folder path for default instance of SQL Server and for named instance "MSSQLServer" has to be replaced with instance name
USE master
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQLDB\DATA'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'Z:\SQLDB\LOG'
GO
USE master
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQLDB\DATA'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'Z:\SQLDB\LOG'
GO
Create Linked Server using Query
EXEC master.dbo.sp_addlinkedserver @server=N'Linked_Server_Name', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'SQL_Instance'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Linked_Server_Name',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='password'GOEXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'data access', @optvalue=N'true'GOEXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'pub', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'rpc out', @optvalue=N'true'GOEXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'use remote collation', @optvalue=N'true'
GO
Friday, July 30, 2010
Avoid Functions on Indexed Columns
Many of us have the have the habit of doing this common mistake of having functions over column names, which makes the column take a totally new value, and by which we make
SQL Server not use the indexes which results in poor performance of a query.
Some of the functions we use generally use in where clause are convert, substring, datediff, etc. Let us understand the performance implications by means of a example below.
SET NOCOUNT ON
SET STATISTICS TIME OFF
IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_FUNCTION_PERF')
DROP TABLE TEST_FUNCTION_PERF
GO
--Create a table with 3 columns
CREATE TABLE TEST_FUNCTION_PERF
( Col1_Int INT IDENTITY(1,1) NOT NULL,
Col2_Date DATETIME NOT NULL,
Col3_Varchar VARCHAR(255) NOT NULL
)
--Let us create clustered index on Date column , it can be on any columns which we would use in where clause
CREATE CLUSTERED INDEX IX_TEST ON TEST_FUNCTION_PERF(Col2_Date)
--Populate table with 90000 records
DECLARE @iCount INTEGER
SELECT @iCount = 1
WHILE @iCount < = 90000 BEGIN INSERT INTO TEST_FUNCTION_PERF(Col2_Date,Col3_Varchar) SELECT GETDATE()- @iCount,'ABC' + CONVERT(VARCHAR,@iCount) SELECT @iCount = @iCount + 1 END Now let us enable statistics to know how our queries perform in terms of CPU Time and Elapsed Time for 3 different queries below which produces same results of 59 rows. SET STATISTICS TIME ON --Query 1: SELECT COL2_DATE FROM TEST_FUNCTION_PERF WHERE CONVERT(VARCHAR(10),COL2_DATE,101) > GETDATE()- 60
--Query 2
SELECT COL2_DATE FROM TEST_FUNCTION_PERF WHERE DATEDIFF(DD,COL2_DATE,GETDATE()) <> DATEADD(DD,-60,GETDATE())
/*
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 438 ms.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 63 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
*/
If you see execution plan of these queries first 2 queries would have gone for Index Scan and the last one would be Index Seek which is optimal and the best way to write such queries is using Query3 format i.e. by keeping index columns separated from functions.
SQL Server not use the indexes which results in poor performance of a query.
Some of the functions we use generally use in where clause are convert, substring, datediff, etc. Let us understand the performance implications by means of a example below.
SET NOCOUNT ON
SET STATISTICS TIME OFF
IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_FUNCTION_PERF')
DROP TABLE TEST_FUNCTION_PERF
GO
--Create a table with 3 columns
CREATE TABLE TEST_FUNCTION_PERF
( Col1_Int INT IDENTITY(1,1) NOT NULL,
Col2_Date DATETIME NOT NULL,
Col3_Varchar VARCHAR(255) NOT NULL
)
--Let us create clustered index on Date column , it can be on any columns which we would use in where clause
CREATE CLUSTERED INDEX IX_TEST ON TEST_FUNCTION_PERF(Col2_Date)
--Populate table with 90000 records
DECLARE @iCount INTEGER
SELECT @iCount = 1
WHILE @iCount < = 90000 BEGIN INSERT INTO TEST_FUNCTION_PERF(Col2_Date,Col3_Varchar) SELECT GETDATE()- @iCount,'ABC' + CONVERT(VARCHAR,@iCount) SELECT @iCount = @iCount + 1 END Now let us enable statistics to know how our queries perform in terms of CPU Time and Elapsed Time for 3 different queries below which produces same results of 59 rows. SET STATISTICS TIME ON --Query 1: SELECT COL2_DATE FROM TEST_FUNCTION_PERF WHERE CONVERT(VARCHAR(10),COL2_DATE,101) > GETDATE()- 60
--Query 2
SELECT COL2_DATE FROM TEST_FUNCTION_PERF WHERE DATEDIFF(DD,COL2_DATE,GETDATE()) <> DATEADD(DD,-60,GETDATE())
/*
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 438 ms.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 63 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
*/
If you see execution plan of these queries first 2 queries would have gone for Index Scan and the last one would be Index Seek which is optimal and the best way to write such queries is using Query3 format i.e. by keeping index columns separated from functions.
Friday, July 9, 2010
Query to get User and Role mappings
--SQL 2000
select 'EXEC sp_addrolemember ''' + g.name + ''',[' + u.name + ']'
from sysusers u, sysusers g, sysmembers m
where g.uid = m.groupuid
and g.issqlrole = 1
and u.uid = m.memberuid
--SQL 2005
select 'EXEC sp_addrolemember @rolename =''' + c.name + ''',@membername = ''' + b.name + '''' from sys.database_role_members a, sysusers b , sys.database_principals c
where b.uid=a.member_principal_id
and a.role_principal_id=c.principal_id
select 'EXEC sp_addrolemember ''' + g.name + ''',[' + u.name + ']'
from sysusers u, sysusers g, sysmembers m
where g.uid = m.groupuid
and g.issqlrole = 1
and u.uid = m.memberuid
--SQL 2005
select 'EXEC sp_addrolemember @rolename =''' + c.name + ''',@membername = ''' + b.name + '''' from sys.database_role_members a, sysusers b , sys.database_principals c
where b.uid=a.member_principal_id
and a.role_principal_id=c.principal_id
Subscribe to:
Posts (Atom)