The following query gives the number of times a stored procedure has been executed since it was last compiled. The time duration is in microseconds and the query below converts them to seconds. It is interesting to note that if you run a stored procedure WITH RECOMPILE, the execution count doesn't go up for that execution but the the previous count remains unchanged
SELECT OBJECT_NAME (object_id, database_id) AS stored_proc_name, s.execution_count, CONVERT (DECIMAL (20, 2), (total_elapsed_time)/1000000) total_elapsed_time_in_secs, CONVERT (DECIMAL (20, 2), (last_elapsed_time/1000000)) last_elapsed_time_in_secs, CONVERT (DECIMAL (20, 2), (min_elapsed_time/1000000)) min_elapsed_time_in_secs, CONVERT (DECIMAL (20, 2), (max_elapsed_time/1000000)) max_elapsed_time_in_secs, CONVERT (DECIMAL (20, 2), (((total_elapsed_time/execution_count)/1000000))) AS avg_elapsed_time_in_secs
FROM sys.dm_exec_procedure_stats s
CROSS APPLY sys.dm_exec_sql_text (s.sql_handle) s1
WHERE database_id = 7
AND [type] = 'P'
Thursday, May 2, 2013
Monday, March 4, 2013
Reducing the VLF count for a SQL Server database
Reducing the number of VLFs in the log files of a database
Having many hundreds or thousands of VLFs in a database's log files increases the recovery time of it. It also is an indication that the log file has grown too frequently by a small amount. I will try to explain how to reduce the number of VLFs and set the growth pattern of the log file to a suitable size
Step 1: Find out how many VLFs are in the log files
Run DBCC LOGINFO under the concerned database's context
The row count of the output indicates the number of VLFs
Step 2: Backup the transaction log
Backup the transaction log to clear it of all the committed transactions. It best to make sure that this manual log backup joins the chain of automated log backups to keep it intact. It's a good idea to simply run the automated job for log backups provided there aren't too many other databases that are part of the job and it is ok to do so
Step 3: Shrink the log file
Having many hundreds or thousands of VLFs in a database's log files increases the recovery time of it. It also is an indication that the log file has grown too frequently by a small amount. I will try to explain how to reduce the number of VLFs and set the growth pattern of the log file to a suitable size
Step 1: Find out how many VLFs are in the log files
Run DBCC LOGINFO under the concerned database's context
The row count of the output indicates the number of VLFs
Step 2: Backup the transaction log
Backup the transaction log to clear it of all the committed transactions. It best to make sure that this manual log backup joins the chain of automated log backups to keep it intact. It's a good idea to simply run the automated job for log backups provided there aren't too many other databases that are part of the job and it is ok to do so
Step 3: Shrink the log file
Shrink the log file using DBCC SHRINK FILE (logical file name,
TargetSize)
The target size
can be accessed by running DBCC SQLPERF ('logspace'). The log space used and
log size should give a rough estimation of the how much space can be gained
If the DBCC
SHRINKFILE does not shrink the log file enough, backup the log file again to
increase the number of inactive VLFs and run DBCC SHRINKFILE again
Step 4
Re-size the log
file and change its auto growth factor
Run the following
with a suitable initial size, max size and growth factor
Preferably, set
the auto growth factor to 1 GB and thus reducing the number of VLFs created
when the file grows
Wednesday, January 2, 2013
Moving a table to a different file group (clustered index move)
Sometimes it becomes necessary to move tables to a different file group. This may be necessary if disk space runs out and a new one is added. In order to use the newly available space, a new file group needs to be created with ndf files assigned to it. Database objects can then be moved to this new file group residing on the newly available space.
Non clustered indexes and clustered indexes can be moved to the new file group
The process of moving a Non clustered index is fairly simple with the CREATE INDEX statement used in conjunction with the DROP EXISTING clause
Clustered indexes however in many cases are created along with the primary key constraint. In such a case, the clustered index cannot be dropped and re-created without dropping the constraint first
Below is an example of a simple table created with a CLUSTERED PRIMARY KEY
CREATE TABLE Index_Drop_Test
( c1 INT NOT NULL
,c2 INT
CONSTRAINT PK_Primary PRIMARY KEY CLUSTERED
)
When we attempt to move this table to another file group by dropping and re-creating the clustered index, the following error is encountered
SQL Statement to drop and re-create clustered index (not the right approach to use DROP INDEX)
DROP INDEX PK_Primary
ON dbo.Index_Drop_Test
Error Message
Msg 3723, Level 16, State 4, Line 1 An explicit DROP INDEX is not allowed on index 'dbo.Index_Drop_Test.PK_Primary'. It is being used for PRIMARY KEY constraint enforcement.
Correct Approach
1) Drop the constraint
ALTER TABLE Index_Drop_Test
DROP CONSTRAINT PK_Primary
2) Re-create the constraint on the secondary file group
ALTER TABLE Index_Drop_Test
ADD CONSTRAINT PK_Primary PRIMARY KEY CLUSTERED (c1)
ON SECONDARY
Wednesday, December 19, 2012
Database Size in MB
SELECT SUM (size*.008) AS database_size_in_MB, DB_NAME (dbid) AS database_name
FROM sys.sysaltfiles
WHERE DB_NAME (dbid) IS NOT NULL
GROUP BY DB_NAME (dbid)
Wednesday, October 31, 2012
Query to find the latest backup date and size
SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name
Monday, September 10, 2012
SQL Server Index Fragmentation Levels Using DMV (Fragmentation greater than 15%)
SELECT DB_NAME(database_id) AS database_name
, so.[name] AS table_name
, dmv.index_id
, si.[name]
, dmv.index_type_desc
, dmv.alloc_unit_type_desc
, dmv.index_depth
, dmv.index_level
, avg_fragmentation_in_percent
, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') dmv
INNER JOIN sysobjects so
ON dmv.[object_id] = so.id
INNER JOIN sys.indexes si
ON
dmv.object_id = si.object_id
AND dmv.index_id = si.index_id
WHERE dmv.avg_fragmentation_in_percent > 15
ORDER BY table_name, si.name
, so.[name] AS table_name
, dmv.index_id
, si.[name]
, dmv.index_type_desc
, dmv.alloc_unit_type_desc
, dmv.index_depth
, dmv.index_level
, avg_fragmentation_in_percent
, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') dmv
INNER JOIN sysobjects so
ON dmv.[object_id] = so.id
INNER JOIN sys.indexes si
ON
dmv.object_id = si.object_id
AND dmv.index_id = si.index_id
WHERE dmv.avg_fragmentation_in_percent > 15
ORDER BY table_name, si.name
Thursday, August 23, 2012
Script to list all indexes in a database with their seek, scan, update count and their last seek, scan, update times
SELECT DB_NAME(database_id) AS [DB Name]
, OBJECT_NAME (dm.object_id) AS [Table Name]
, si.[name]
, user_seeks AS [User Seeks]
, user_scans AS [User Scans]
, user_lookups AS [User Lookups]
, user_updates AS [User Updates]
, last_user_seek AS [Last User Seek]
, last_user_scan AS [Last User Scan]
, last_user_lookup AS [Last User Lookup]
, last_user_update AS [Last User Update]
--, *
FROM sys.dm_db_index_usage_stats dm
INNER JOIN sys.indexes si
ON si.index_id = dm.index_id
AND dm.object_id = si.object_id
INNER JOIN sysobjects so
ON so.id = si.object_id
WHERE dm.database_id = DB_ID()
AND si.name IS NOT NULL
AND OBJECTPROPERTY (dm.object_id, 'IsUserTable') = 1
ORDER BY COALESCE (last_user_seek, last_user_scan, last_user_lookup, last_user_update)
SELECT DB_NAME(database_id) AS [DB Name]
, OBJECT_NAME (dm.object_id) AS [Table Name]
, si.[name]
, user_seeks AS [User Seeks]
, user_scans AS [User Scans]
, user_lookups AS [User Lookups]
, user_updates AS [User Updates]
, last_user_seek AS [Last User Seek]
, last_user_scan AS [Last User Scan]
, last_user_lookup AS [Last User Lookup]
, last_user_update AS [Last User Update]
--, *
FROM sys.dm_db_index_usage_stats dm
INNER JOIN sys.indexes si
ON si.index_id = dm.index_id
AND dm.object_id = si.object_id
INNER JOIN sysobjects so
ON so.id = si.object_id
WHERE dm.database_id = DB_ID()
AND si.name IS NOT NULL
AND OBJECTPROPERTY (dm.object_id, 'IsUserTable') = 1
ORDER BY COALESCE (last_user_seek, last_user_scan, last_user_lookup, last_user_update)
Subscribe to:
Posts (Atom)