Category Archives: SQL Server Scripts

Buffer pool: Look at the contents of Buffer Pool

Buffer Management http://msdn2.microsoft.com/en-us/library/aa337525.aspx http://blogs.msdn.com/sqlperf/default.aspx The following script give you the info about buffer pool and written by "Data & SQL Storage Performance Team" select        count(*)as cached_pages_count,        obj.name as objectname,        ind.name as indexname,        obj.index_id as indexid from … Continue reading

Posted in SQL Server Scripts | 21 Comments

Script to find used and un-used indexes in SQL Server 2005

Script to find Used indexes in the current database… SELECT so.name AS ‘Table Name’, si.name AS ‘index Name’, si.type_desc AS ‘Index Type’, si.index_id AS ‘Index ID’, us .user_seeks, us.user_scans, us.system_seeks, us.system_scans FROM sys.dm_db_index_usage_stats us INNER JOIN sys.objects so ON us.object_id … Continue reading

Posted in SQL Server Scripts | 100 Comments

Pagesplit script

For SQL Server 2005 select getDate() as collected, [AllocUnitName] , count ([Current LSN]) as countSplitsfrom FROM ::fn_dblog(null, null) where Operation = N‘LOP_DELETE_SPLIT’ group by [AllocUnitName]   For SQL Server 2000   select getDate() as collected, [Object Name] , [Index Name] … Continue reading

Posted in SQL Server Scripts | 13 Comments

Server installation and configuration info script

I used the following script to generate the SQL Server installation and configuration information many times…It works fine i SQL server 2000 and SQL Server 2005… I am planning to add few more columns using DMVs …. And also there … Continue reading

Posted in SQL Server Scripts | 100 Comments

xp_execresultset procedure alternative in SQL 2005

I used xp_execresultset  procedure extensively in my Admin scripts in sql server 2000, now it has been removed in sql server 2005. When I was searching I found the article written Adam Mechanic and alter native script for xp_execresultset  procedure. … Continue reading

Posted in SQL Server Scripts | 55 Comments

Calculate I/O stalls

The following scrip from SQL pass to calculate I/O stalls in SQL Server 2005….   Calculate avg. I/O stalls: select DB_NAME(database_id) , [file_id] , io_stall_read_ms , num_of_reads , cast (io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as ‘avg_read_stall_ms’ , io_stall_write_ms , num_of_writes , cast … Continue reading

Posted in SQL Server Scripts | 39 Comments

Grant user permission script…

Granting Permissions to user script…  SQL 2000 should work in 2005 but not tested DECLARE tnames_cursor CURSORFOR   SELECT name from sysobjects where xtype=’U’ OPEN tnames_cursorDECLARE @tablename sysnameFETCH NEXT FROM tnames_cursor INTO @tablenameWHILE (@@FETCH_STATUS <> -1)BEGIN   IF (@@FETCH_STATUS <> -2)   BEGIN   … Continue reading

Posted in SQL Server Scripts | 31 Comments