SQL Cachestore flush

For each cleared cachestore in the plan cache SQL Server reports a message in the error logs  , such as :

 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

 Notes:

1)       When the plan cache clears ,  a recompilation is forced , this can cause a temporary performance decrease

2)       Using DBCC FREEPROCCACHE  clears the cache .

3)       Restoring a database and attaching\detaching a database  causes a plan cache clear for the SQL Server instance.

4)       Using DBCC FREESYSTEMCACHE clears the cache.

5)       AUTOCLOSE = ON which causes the database to shutdown forces a clear down of cache.

6)     Buffer manager sql counters and sys.dm_os_memoryclerks  are good starting points to identify pressure

 See Also

SQL Server page life expectancy and memory bottleneck

Sys.dm_os_memory_clerks and AWE memory allocation

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Performance Tuning - optimizing for response time

Different  queries are optimized for different objectives

Response Times and Throughput are two standard objectives.

Response Time is about retrieving the  hits as quickly as possible. In a search engine , that would mean returning the first few rows immediately

Throughput is about returning ALL the hits (rows) in the smallest time frame

Factors to consider in improving Response Time

1)       Parallelism

2)       Partitioning

3)       Data types – such as CLOBs in the table

4)       Sorting

5)       Memory

6)       SQL Statistics

See Also

Waitstats CXPACKET wait type and how to reduce it

SQL Server Performance ,memory pressure and memory usage

SQL Server Buffer Cache Hit Ratio and memory pressure

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server – Logical Disk Read Bytes/sec and disk io

In the SAN environment , the Logical Disk Performance objects counters monitor the logical partitions.

 I use the Logical Disk Read Bytes/sec  as a starting point when I troubleshoot Disk IO issues. If I can isolate the activity , this gives me a good guideline as to Read rates from the SAN as viewed from the OS.

 The Logical Disk Read Bytes/sec  and the Logical Disk Writes Bytes/sec  display rates of transfer for read\write .

Check SQL Server – Backup to NUL trick - read throughput as an example of how to use the counter to troubleshoot disk IO .

Use Perfmon to initiate a monitor. Isolating the activity onto one disk and stopping all other activity is a good starting point to define the rates.

See Also

Window Perfmon scripting, SQL Server perfmon and how to perfmon

IO_completion on disk solved with Wait Stats and Perfmon

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server – Backup to NUL trick - read throughput

A neat test for read speed backup throughput is to use the Backup to NUL .

NUL is a special “file”. It's as a nul device , anything  “written” to it is discarded.Don’t confuse with the SQL reserved word NULL.

Before you run this statement , note that even as the file is not going anywhere , SQL Server still considers it to be valid BACKUP – therefore any subsequent LOG backups would be invalid , until you commit another FULL BACKUP.

Use the COPY_ONLY  option to avoid this problem

   1:  backup database MY_DB to disk = 'NUL:' with stats = 10
Sample output:
Processed 1706648 pages for database 'MY_DB', file 'MY_DB' on file 1.
100 percent processed.
Processed 38 pages for database 'MY_DB', file 'MY_DB_log' on file 1.
BACKUP DATABASE successfully processed 1706686 pages in 28.221 seconds 
(495.417 MB/sec).

 

Accompany the statement with  : Logical Disk Read Bytes/sec on relevant disk  to obtain read throughput

 See Also

Copy only backup in SQL server 2005 and not break the backup chain

Disk IO performance , disk block size tuning and SQL Servers

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL SERVER REBUILD INDEX

Use the script below to rebuild an index on a table

ALTER INDEX   MY_INDEX_NAME on MY_TABLE_NAME  REBUILD  ; 

 Check SQL Server BOL for a the full list of options

 Notes

1)  Issuing an Index Rebuild request , drops and recreates the index

2) If  rebuilding a  CLUSTERED INDEX ,  nonclustered indexes won’t be rebuilt unless ALL keyword is used.

3) If no options are added , the statement inherits the options in sys.indexes

Why rebuild an index?

1)       Before rebuilding an index – ask the question “is the gain worth the cost of rebuilding the index?”  Rebuilding an index  can be a costly exercise , particularly on very large indexes.

2)       Strategy for large imports of data .  A typical strategy is disable NONCLUSTERED INDEXES , import the data and  rebuild the NONCLUSTERED INDEXES

3)       Index troubleshooting

4)     Index disable is issued  and the index needs to be enabled

Related Posts:

Disable Index and Rebuild Index for Updates on very large tables

SQL SERVER DISABLE INDEX

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)