SQL Syntax

Indexing best practices

1)   Periodically identify potentially missing indexes.

2)   Drop indexes that are never used.

3)   Don’t create redundant indexes.

4)   Generally every table should have at least a clustered index, but not always. The clustered index should be on a column that monotonically increases and value is unique.

5)   You can only create one clustered index per table, think carefully how it will be used.

6)   When you create a composite index, make the most selective column the first column of the index.

7)   Keep the “width” of your indexes as narrow as possible. It’ll reduce the size of the index and reduces the number of disk I/O to improve the performance.

8)   Avoid adding a clustered index on VARCHAR & NVARCHAR column with large width, which makes the index larger, it’ll increase I/O reads, and can hurt performance.

9)   Indexes should be considered on those columns which are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.

10) Add indexes if you know that they will be used by queries run against the table.

11) When creating indexes, try to make them unique indexes if possible. SQL Server can often search through a unique index faster than a non-unique index.

12) Joins between two or more tables in your queries, performance will be optimized if each of the joined columns has proper indexes.

13) A high FILL FACTOR is good for seldom changed data, so consider carefully index FILL FACTOR.

14) Don’t over index your OLTP tables.

Indexes and their usage details:-

DBA to know how the indexes of tables are doing, the below script will let you know the usage of indexes. Based on the column “total_scan” I’ll be able to tell which indexes are mostly used and what is the associated table. This information makes a DBA to keep an eye on those indexes because fragmentations of them may create a performance bottleneck.

SELECT

o.name [Object_Name],

SCHEMA_NAME(o.[schema_id]) [Schema_name],

DB_NAME(s.database_id) Database_Name,

i.name Index_name,

i.Type_Desc,

s.user_seeks,

s.user_scans,

s.user_lookups,

(s.user_seeks + s.user_scans) AS total_scan,

s.user_updates,

s.system_seeks,

s.system_scans,

s.system_lookups,

getdate() Stat_Date

FROM

sys.objects AS o INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id]

INNER JOIN sys.dm_db_index_usage_stats AS s ON i.[object_id] = s.[object_id]

AND i.index_id = s.index_id     AND DB_ID() = s.database_id

WHERE

o.type = ‘u’

AND i.type IN (1, 2)

AND (s.user_seeks > 0 OR s.user_scans > 0 OR s.user_lookups > 0

OR s.system_seeks > 0 OR s.system_scans > 0 OR s.system_lookups > 0)

ORDER BY (s.user_seeks + s.user_scans) DESC;

Missing Indexes:- How to find out ?

The below script will let you know the missing indexes in the selected database, this script is also an important for DBA to know time to time that what are the missing indexes are there because the information of below script will be generated by the actual usage of buffer pool.

SELECT

S.AVG_TOTAL_USER_COST * (S.AVG_USER_IMPACT / 100.0) * (S.USER_SEEKS + S.USER_SCANS) AS EST_IMPROVE,

S.AVG_USER_IMPACT, LEFT (PARSENAME(D.STATEMENT, 1), 32) TABLE_NAME,

ISNULL (D.EQUALITY_COLUMNS,”) + CASE WHEN D.EQUALITY_COLUMNS IS NOT NULL AND D.INEQUALITY_COLUMNS IS NOT NULL THEN ‘,’

ELSE ” END + ISNULL (D.INEQUALITY_COLUMNS, ”) COLUMN_NAMES, ISNULL (D.INCLUDED_COLUMNS , ”) AS INCLUDE_COLUMNS,

S.UNIQUE_COMPILES, S.USER_SEEKS, S.USER_SCANS, S.LAST_USER_SEEK, S.LAST_USER_SCAN, S.AVG_TOTAL_USER_COST,

S.SYSTEM_SEEKS, S.SYSTEM_SCANS, S.LAST_SYSTEM_SEEK, S.LAST_SYSTEM_SCAN, S.AVG_TOTAL_SYSTEM_COST, S.AVG_SYSTEM_IMPACT

FROM

SYS.DM_DB_MISSING_INDEX_GROUPS G INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS S ON S.GROUP_HANDLE = G.INDEX_GROUP_HANDLE

INNER JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON G.INDEX_HANDLE = D.INDEX_HANDLE

ORDER BY EST_IMPROVE DESC ;

Index Property table wise:-

The below script will let you know the count of clustered, non-clustered indexes on a table and index size.

SELECT

sys.tables.name AS [TABLE],

sys.tables.create_date AS CREATE_DATE,

sys.tables.modify_date AS MODIFY_DATE,

CASE WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id) ELSE sys.database_principals.name END AS OWNER,

SUM(ISNULL(CASE INDEXES.TYPE WHEN 0 THEN COUNT_TYPE END, 0)) AS COUNT_HEAP_INDEX,

SUM(ISNULL(CASE INDEXES.TYPE WHEN 1 THEN COUNT_TYPE END, 0)) AS COUNT_CLUSTERED_INDEX,

SUM(ISNULL(CASE INDEXES.TYPE WHEN 2 THEN COUNT_TYPE END, 0)) AS COUNT_NONCLUSTERED_INDEX,

SUM(ISNULL(CASE INDEXES.TYPE WHEN 3 THEN COUNT_TYPE END, 0)) AS COUNT_XML_INDEX,

SUM(ISNULL(CASE INDEXES.TYPE WHEN 4 THEN COUNT_TYPE END, 0)) AS COUNT_SPATIAL_INDEX,

sys.tables.max_column_id_used AS COUNT_COLUMNS,

sys.partitions.rows AS COUNT_ROWS,

SUM(ISNULL(CASE WHEN sys.allocation_units.type <> 1 THEN USED_PAGES WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) *

(SELECT low / 1024 AS VALUE FROM master.dbo.spt_values WHERE (number = 1) AND (type = N’E’)) AS SIZE_DATA_KB,

SUM(ISNULL(sys.allocation_units.used_pages – CASE WHEN sys.allocation_units.type <> 1 THEN USED_PAGES

WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) * (SELECT low / 1024 AS VALUE

FROM

master.dbo.spt_values AS spt_values_2

WHERE (number = 1)

AND (type = N’E’)

) AS SIZE_INDEX_KB

FROM

sys.allocation_units INNER JOIN sys.partitions ON sys.allocation_units.container_id = sys.partitions.partition_id

INNER JOIN

(

SELECT TOP (100) PERCENT object_id, index_id, type AS TYPE, COUNT(*) AS COUNT_TYPE

FROM sys.indexes AS indexes_1 GROUP BY object_id, type, index_id ORDER BY object_id

) AS INDEXES ON

sys.partitions.object_id = INDEXES.object_id

AND sys.partitions.index_id = INDEXES.index_id

RIGHT OUTER JOIN sys.database_principals

RIGHT OUTER JOIN sys.tables ON sys.database_principals.principal_id = sys.tables.principal_id

ON INDEXES.object_id = sys.tables.object_id

GROUP BY sys.tables.name, sys.tables.create_date, sys.tables.modify_date,

CASE WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id) ELSE sys.database_principals.name END,

sys.tables.max_column_id_used, sys.partitions.rows

ORDER BY COUNT_ROWS DESC;

Good Luck

* List all tables without any sort of index at all

create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE object_id IN
(
SELECT object_id
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,’IsIndexed’) = 0
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = ‘U’
order by i.rows desc
drop table #Ttable

* Code to get tables without any Clustered indexes and the number of rows in the tables. This code is for sql 2005/8/12:

create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE NOT EXISTS
(
SELECT * FROM sys.indexes AS i
WHERE i.object_id = t.object_id
AND i.type = 1 — or type_desc = ‘CLUSTERED’
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = ‘U’
order by i.rows desc
drop table #Ttable

* Code to catch long running spids:
This code and alterations of it can be used to list spids that have been running for a logn time. Spids up to 50 are system spids so i remove them in the where clause. Many system spids opens up when the service starts and are then running until the service stops. I have here only listed spids running for the sqlagent. I can also remove that part and get all long running queries. If i change days to hours i can narrow it down etc. When checking this for Ramona we found several old spids from their Activepearl application.

SELECT DATEDIFF(day,last_batch,getdate() )AS DaysRunning ,spid,blocked,waittime,db_name(dbid)AS DatabaseName,user_name(uid)AS Username,
physical_io,login_time,last_batch,open_tran,status,program_name,hostprocess,cmd,nt_username,net_library,loginame
FROM master.dbo.sysprocesses (NOLOCK)
WHERE DATEDIFF ( day , last_batch,getdate() ) >1
and spid >50 and program_name like ‘sqlagent%’

ORDER BY daysrunning DESC
* Some nice code to be used when we investigate performance problems on sql server:

— Gives info regarding how the indexes have been used, seeks, scan, updates etc.
select * from sys.dm_db_index_usage_stats

— Gives info about e.g. fragmentation, type, depth etc
SELECT * FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’)

— gives wait times info for locks, latches, io etc.
select * from sys.dm_os_wait_stats

— Clear the info so we can test again after changes
DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);
GO

— Gives info about how queries was executed, min and max writes, read elapsed time, clr time etc..
select * from sys.dm_exec_query_stats

There are several other interesting system views on sql server for many other situations.

* View changes in DDL:
Often we need to look for changes in a customers database. A clue to this can be found in the system table sysobjects. Everytime an object is changed, it is logged here. I use this code to list the changes:

SELECT name, crdate,schema_ver,type
FROM sysobjects
ORDER BY crdate DESC

The reslut tells me the name of the changed object, time and what type it is. The types are:

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure’

* Code to list all tables without primarykey and the number of rows in sql 2005/8/12

create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.key_constraints
WHERE type_desc = ‘PRIMARY_KEY_CONSTRAINT’ — or type = ‘PK’
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = ‘U’
order by i.rows desc
drop table #Ttable

* Code to list changes of tables, views, indexes etc in the last x days
Change the number 20 to the days back in time you whant to check. Works with sql server 2005/8/12

SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() – 20
ORDER BY modify_date;

* This code can be used to identify all indexed that never have been used.
This lists all indexes that have not been used since the last start up of sql server 2005 instance. The index have bene updated (caused IO and CPU) but never referenced, used, scaned, seeked in etc. Note, before you delete any index, always run this for a long time. It might be that the code using the index just runs once every 3 month or so! This gives us a very good vire of how the indexes is not used anyway.

select object_name(i.object_id),
i.name,
s.user_updates,
s.user_seeks,
s.user_scans,
s.user_lookups
from sys.indexes i left join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = 5
where objectproperty(i.object_id, ‘IsIndexable’) = 1 and
— index_usage_stats has no reference to this index (not being used)
s.index_id is null or
— index is being updated, but not used by seeks/scans/lookups
(s.user_updates > 0 and s.user_seeks = 0
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id) asc

* Eventid:
When tracing we often get eventid. This code will list all eventid:s and the explanation.

SELECT DISTINCT T.eventid, E.name
FROM fn_trace_geteventinfo(1) T
JOIN sys.trace_events E
ON T.eventid = E.trace_event_id
GO

This is what you might see:

Database

Data file auto grow
Data file auto shrink
Database mirroring status change
Log file auto grow
Log file auto shrink
Errors and Warnings

Errorlog
Hash warning
Missing Column Statistics
Missing Join Predicate
Sort Warning
Full-Text

FT Crawl Aborted
FT Crawl Started
FT Crawl Stopped
Objects

Object Altered
Object Created
Object Deleted
Security Audit

Audit Add DB user event
Audit Add login to server role event
Audit Add Member to DB role event
Audit Add Role event
Audit Add login event
Audit Backup/Restore event
Audit Change Database owner
Audit DBCC event
Audit Database Scope GDR event (Grant, Deny, Revoke)
Audit Login Change Property event
Audit Login Failed
Audit Login GDR event
Audit Schema Object GDR event
Audit Schema Object Take Ownership
Audit Server Starts and Stops

ETC

* Kill selected spids

Sometimes we need to kill a bunch of spids but maybe not everyone. This is an example code so kill selected spids.

For we needed to kill all spids but the spids comming from the authoring environment.
This code will kill all spids but the ones coming from authoring and my own spid. Just change there where caluse for any other need.

CREATE PROCEDURE usp_KillUsers as
SET NOCOUNT ON DECLARE @strSQL varchar(255) PRINT ‘Killing Users’
PRINT ‘—————–‘ CREATE table #tmpUsers( spid int, ECID INT,
status varchar(30), loginname varchar(50), hostname varchar(50),
blk int, dbname varchar(50), cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO DECLARE LoginCursor
CURSOR READ_ONLY FOR SELECT spid, dbname FROM #tmpUsers WHERE spid > 50 and
(hostname <>’GBW02221’ AND hostname <> ‘GBW02222’ ) and spid <>(select @@spid)
DECLARE @spid varchar(10) DECLARE @dbname2 varchar
(40) OPEN LoginCursor FETCH NEXT FROM LoginCursor INTO @spid,
@dbname2 WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2)
BEGIN PRINT ‘Killing ‘ + @spid SET @strSQL = ‘KILL ‘ + @spid exec
(@strSQL) END FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 END
CLOSE LoginCursor DEALLOCATE LoginCursor DROP table #tmpUsers
PRINT ‘Done’

* You can run this on your database and know unknown of your databases as well.

SELECT database_id, CONVERT(VARCHAR(25), DB.name) AS dbName, CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, ‘status’)) AS [Status], state_desc,  (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS DataFiles,  (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS [Data MB],  (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS LogFiles,  (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS [Log MB], user_access_desc AS [User access], recovery_model_desc AS [Recovery model], CASE compatibility_level WHEN 60 THEN ’60 (SQL Server 6.0)’ WHEN 65 THEN ’65 (SQL Server 6.5)’ WHEN 70 THEN ’70 (SQL Server 7.0)’ WHEN 80 THEN ’80 (SQL Server 2000)’ WHEN 90 THEN ’90 (SQL Server 2005)’ WHEN 100 THEN ‘100 (SQL Server 2008)’ END AS [compatibility level], CONVERT(VARCHAR(20), create_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],

* Last Backup Database:

ISNULL((SELECT TOP 1 CASE TYPE WHEN ‘D’ THEN ‘Full’ WHEN ‘I’ THEN ‘Differential’ WHEN ‘L’ THEN ‘Transaction log’ END + ‘ – ‘ + LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ‘ days ago’, ‘NEVER’)) + ‘ – ‘ + CONVERT(VARCHAR(20), backup_start_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_start_date, 108) + ‘ – ‘ + CONVERT(VARCHAR(20), backup_finish_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_finish_date, 108) + ‘ (‘ + CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ‘ ‘ + ‘seconds)’ FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),’-‘) AS [Last backup], CASE WHEN is_fulltext_enabled = 1 THEN ‘Fulltext enabled’ ELSE ” END AS [fulltext], CASE WHEN is_auto_close_on = 1 THEN ‘autoclose’ ELSE ” END AS [autoclose], page_verify_option_desc AS [page verify option], CASE WHEN is_read_only = 1 THEN ‘read only’ ELSE ” END AS [read only], CASE WHEN is_auto_shrink_on = 1 THEN ‘autoshrink’ ELSE ” END AS [autoshrink], CASE WHEN is_auto_create_stats_on = 1 THEN ‘auto create statistics’ ELSE ” END AS [auto create statistics], CASE WHEN is_auto_update_stats_on = 1 THEN ‘auto update statistics’ ELSE ” END AS [auto update statistics], CASE WHEN is_in_standby = 1 THEN ‘standby’ ELSE ” END AS [standby], CASE WHEN is_cleanly_shutdown = 1 THEN ‘cleanly shutdown’ ELSE ” END AS [cleanly shutdown] FROM sys.databases DB ORDER BY dbName, [Last backup] DESC, NAME

* Complete info about all databases:

SELECT  database_id,  convert(varchar(25), DB.name) as dbName,  convert(varchar(10), Databasepropertyex(name, ‘status’)) as [Status],  state_desc,  (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS DataFiles,  (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS [Data MB],  (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS LogFiles,  (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS [Log MB],  user_access_desc AS [User access],  recovery_model_desc as [Recovery model],  CASE compatibility_level  WHEN 60 THEN ’60 (SQL Server 6.0)’ WHEN 65 THEN ’65 (SQL Server 6.5)’ WHEN 70 THEN ’70 (SQL Server 7.0)’ WHEN 80 THEN ’80 (SQL Server 2000)’ WHEN 90 THEN ’90 (SQL Server 2005)’ END AS [compatibility level],  CONVERT(VARCHAR(20), create_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), create_date, 108) as [Creation date],

* Last backup:

ISNULL((SELECT TOP 1  CASE TYPE WHEN ‘D’ THEN ‘Full’ WHEN ‘I’ THEN ‘Differential’ WHEN ‘L’ THEN ‘Transaction log’ END + ‘ – ‘ + ltrim(ISNULL(STR(ABS(DATEDIFF(day, GetDate(),Backup_finish_date))) + ‘ days ago’, ‘NEVER’)) + ‘ – ‘ + CONVERT(VARCHAR(20), backup_start_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_start_date, 108) + ‘ – ‘ + CONVERT(VARCHAR(20), backup_finish_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_finish_date, 108) + ‘ (‘ + CAST(DATEDIFF(second, BK.backup_start_date,  BK.backup_finish_date) AS VARCHAR(4)) + ‘ ‘ + ‘seconds)’ FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),’-‘) AS [Last backup],

CASE WHEN is_fulltext_enabled = 1 THEN ‘Fulltext enabled’ ELSE ” END AS [fulltext],  CASE WHEN is_auto_close_on = 1 THEN ‘autoclose’ ELSE ” END AS [autoclose],  page_verify_option_desc AS [page verify option],  CASE WHEN is_read_only = 1 THEN ‘read only’ ELSE ” END AS [read only],  CASE WHEN is_auto_shrink_on = 1 THEN ‘autoshrink’ ELSE ” END AS [autoshrink],  CASE WHEN is_auto_create_stats_on = 1 THEN ‘auto create statistics’ ELSE ” END AS [auto create statistics],  CASE WHEN is_auto_update_stats_on = 1 THEN ‘auto update statistics’ ELSE ” END AS [auto update statistics],  CASE WHEN is_in_standby = 1 THEN ‘standby’ ELSE ” END AS [standby],  CASE WHEN is_cleanly_shutdown = 1 THEN ‘cleanly shutdown’ ELSE ” END AS [cleanly shutdown]  FROM sys.databases DB  ORDER BY dbName, [Last backup] DESC, NAME

SQL SERVER – Enable Automatic Statistic Update on Database:

SELECT is_auto_create_stats_on,is_auto_update_stats_on  FROM sys.databases  WHERE name = ‘YOUR DATABASE NAME HERE’

🙂              🙂                🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: