Luckily, two of the internal Microsoft tools used in order to diagnose slowdowns is also available for the publics use. These tools are SQLDiag (previously PSSDiag), and SQL Nexus. SQLDiag (short for SQL Diagnostics) monitors all aspects of the system that SQL Server is running on and generates all output needed in order to determine the bottleneck. The difficult challenge that follows is how to interpret that data. This is where SQL Nexus comes in. SQL Nexus is an application written on the .NET framework, that utilizes a client distribution of Reporting Services in order to give us insight into the output generated from SQLDiag. While it is relatively hopping along on 3 legs, we are still very lucky to have it. This video walks you through the initial process of setting up the traces and gives a brief intro in the data you can expect to find. In order to download, following this link and read the installation carefully. [code] http://sqlnexus.codeplex.com/ [/code] The installation guide will also walk you through downloading the ReadTrace tool (contained in the Microsoft RML tools download). And it will also tell you to download the PerfStats scripts which are used in order to initiate the SQLDiag trace. These are also important.
Continue reading ...
Alter index is a powerful new command that replaces both the DBCC INDEXDEFRAG and the DBCC DBREINDEX, and at the same time enhances them both. The following example encapsulates the most common settings used when rebuilding an index. This example is the same as using the DBCC DBREINDEX on an entire table. The settings below are simply detailed so you can uncomment or set them yourself. They are all set to the default: [cc lang=”sql”] ALTER INDEX ALL — All indexes on Table ON Sales.SalesOrderDetail REBUILD — REBUILD OR REORGANIZE WITH ( –FILLFACTOR = 80, — Leaf page pad % PAD_INDEX = OFF, — Pads Intermediate pages SORT_IN_TEMPDB = OFF, STATISTICS_NORECOMPUTE = OFF, — Auto update stats on ONLINE = OFF, — ON for concurrency ALLOW_ROW_LOCKS = ON, — Allow row locking ALLOW_PAGE_LOCKS = ON, — Allow page locking MAXDOP = 0, DATA_COMPRESSION = NONE — NONE | ROW | PAGE ) [/cc] Let’s look at what all this means. Setting Values Description [Indexes] ALL | (Specify an index name) All – Applies the settings to all the indexes on the table. Or alternatively specify an index name to isolate one index. [Alter Type] REBUILD | REORGANIZE REBUILD completely rebuilds the indexes, while REORGANIZE simply defgraments them. This is akin to DBCC DBREINDEX VS DBCC INDEXDEFRAG. Rebuild is more thorough. FILLFACTOR 0 – 100 Specifies the percentage of free space to be left for pages at the leaf level. Higher the number helps with quicker seeks, but you will need […]
Continue reading ...
I had to go back through this article and break it out in terms of Pro’s and Con’s. Pro’s of using Alter Procedure Permissions stay intact ObjectId’s of procedures stay the same Altering a procedure is transactional, dropping is not The create date of the procedure remains as original, and modified date gets updated Pro’s of using Drop / Create for a Procedure Objects can be rebuilt in a new location using source control (does not need to already exist) You don’t need to worry if the procedure already exists to determine how to write it I’ve mostly used drop create in places where I’ve worked, but I’ve just been convinced to use Alter (where applicable) instead. The script below will show the original procedure’s create date and modified date: [cc lang=”sql”] SELECT CREATED, LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES [/cc] As for the permissions issue, two things come up. The first is that I don’t set my permissions at the object level. I set them actually at the schema level (for 2005+). This comes in handy because whatever schema you create an object under, automatically inherits those permissions. However, if you have a DBA that sets more granular permissions on the objects, then drop/create will get you in trouble, because those permissions will forever be lost. Another issue with drop/create, is that in a highly transactional system, if you execute the drop statement and a call comes in prior to the create, you will receive errors. Alter will not do this however […]
Continue reading ...
Increasingly, you can count on most DBCC commands as being deprecated in future versions of SQL. This is one of the ones that will be removed later. It still servers a purpose. However it can be replaced using the ALTER INDEX command. The great thing about the DBCC DBReindex command is that it will completely rebuild a totally fragmented or corrupted index. This is likely to happen on a busy production system. The most basic syntax is: [cc lang=”sql”] DBCC DBREINDEX (‘Person.Address’) [/cc] This will rebuild all indexes on the Person.Address table with the default fill factor. Optionally, you can also re-index only one index on the table and specify a specific fill factor. To do that, simply add the following two parameters: [cc lang=”sql”] DBCC DBREINDEX (‘Person.Address’, ‘PK_Address_AddressID’, 90) [/cc] This will only re-index one index, in this case the primary key, with a fill factor of 90%. Meaning 90% of the data pages will be full, leaving 10% for splits. Something to note with DBCC DBReindex is that it will take your operation offline. Meaning it will block any operations that occur against the table during the duration of the rebuild. There is a way around this. It is to use the ALTER INDEX command. Here is the alternative. [cc lang=”sql”] ALTER INDEX ALL ON Person.Address REBUILD WITH (ONLINE = ON, FILLFACTOR = 90) [/cc] This command will rebuild all the indexes on the Person.Address table with a fill factor of 90%. This will all happen online, meaning […]
Continue reading ...
The DBCC FREEPROCCACHE command clears the procedure cache for all the procedures on the server. This command should be used with caution, however in most circumstances it will not bring a server to its knees. In order to execute in SQL 2005+, you need to have the server-level permissions of ALTER SERVER STATE. To use it, simple execute this: [cc lang=”sql”] DBCC FREEPROCCACHE [/cc] From a production standpoint, this command is sometimes used when it has been determined that execution plans have gone bad. This is typically a rare event and can be determined by comparing the baseline duration of commonly executed procedures. Usually if one procedure is determined to be bad a better thing to execute is sp_recompile. From a development standpoint, this command is used in order to clear the procedure cache in order to get a baseline for the execution time of statements or stored procedures. In SQL Server 2008, the command can now accept a SQL Handle, Plan Handle, or a Resource Governor Pool Number, allowing you to isolate what cache you are clearing. [cc lang=”sql”] DBCC FREEPROCCACHE (0x03000500462D954470E2FF00BE9D00000100000000000000) [/cc]
Continue reading ...
