Check Database Fragmentation -SQL 2000

Again as requested, I have come up with a script to check
fragmentation for SQL 2000. This one as expected works using DBCC
Showcontig.Fairly simple script. Let us take a look

CREATE TABLE #fraglist

(

ObjectName CHAR(255),

ObjectId INT ,

IndexName CHAR(255),

IndexId INT ,

Lvl INT ,

CountPages INT ,

CountRows INT ,

MinRecSize INT ,

MaxRecSize INT ,

AvgRecSize INT ,

ForRecCount INT ,

Extents INT ,

ExtentSwitches INT ,

AvgFreeBytes INT ,

AvgPageDensity INT ,

ScanDensity DECIMAL ,

BestCount INT ,

ActualCount INT ,

LogicalFrag DECIMAL ,

ExtentFrag DECIMAL

);

INSERT

INTO #fraglist EXEC

(

'DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES'

)

SELECT SUM((Countpages * 8 ))/1024.0 AS Available_Space_in_MB,

CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 AS Space_Used_in_MB ,

(CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 )/( SUM((Countpages * 8 ))/1024.0 ) * 100 AS Percentage_Used ,

( SUM(

CASE

WHEN indexid IN (0,255)

THEN 0

ELSE CountPages * LogicalFrag

END /100.0) / SUM(CountPages ) * 100) [Total_Logical_Fragmentation_in_%],

( SUM(

CASE

WHEN indexid IN (0,255)

THEN 0

ELSE Extents * ExtentFrag

END /100.0)/ SUM(Extents ) * 100) AS [Total_Extent_Fragmentation_in_%],

(CONVERT(DECIMAL,SUM(BestCount))/CONVERT(DECIMAL,SUM(ActualCount))) * 100 AS Scan_Density

FROM #fraglist

WHERE ActualCount!=0

AND Extents !=0

AND CountPages !=0

Let me run thro this script briefly. A temporary table
#fraglist is created.

The Showcontig results for each table in the database are dumped
into temp table using the command

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES.

A query to give a consolidated report at database level is ran
against the temp table.

A quick run thro results provided. Most of columns have the same
definition as provided in my script for SQL 2005.But, there are few differences as well.

Available_Space_used_in_MB: Available_Space_used_in_MB is the sum of the space allocated to each table/index in the database.Available_Space_used_in_MB has
the same definition as the one in SQL 2005 script but the difference is that in 2k Script I have
expressed it in MB, but in 2k5 script I have expressed it in pages.Also, realise the difference between Available_Space_used_in_MB and the actual database size. The actual database size would include the space occupied by dropped objects and deleted objects. Unless you shrink your database, the database doesnt release the space occupied by dropped objects or deleted
rows. So, total database size would be always greater than the Available_Space_used_in_MB value provided by this query.

Space_Used_in_Mb: Space_Used_in_Mb is the sum of space actually used by each table.

Percentage Used: Percentage of Available_Space_used_in_MB used. Expected to be around 90%+ or close to the fillfactors set for your bigger tables.

Total_Logical_Fragmentation_in_%: Total_Logical_Fragmentation_in_%
percentage of out of order pages. As mentioned in my previous post less than 10% is
acceptable. Logical fragmentation applies only for indexed tables. and for not for heaps. If
logical fragmentation is greater than 30% go for a Rebuild or if its between 10 to 30% then DBCC INDEXDEFRAG(Databasename,tablename) would do.

Total_Extent_Framentation_in_%:
Total_Extent_Framentation_in_% refers to the percentage of extents that are out of order. Extent Fragmentation displayed by Showcontig doesnt apply for heaps.Please note the difference between script used for SQL 2k5 and 2k. In 2k5, sys.Dm_db_index_physical_stats
shows Extent fragmentation values only for heaps and not for indexed tables. But in Showcontig used in SQL 2000, Extent fragmentation applies only for indexed tables and not for
heaps at all. Again as mentioned before acceptable value for extent fragmentation is less than 10%.

Scan Density: Scan density is the ratio of minimum number of extents required to hold the table ( page count/8) and the number of extent switches required by the DBCC command to
scan the table, expressed in %. Extent switches basically represent the number of jumps made from one extent to another while accessing the entire table. Scan density Percentage is
common for both indexed tables and heaps. So, Scan density percentage can be used to measure the fragmentation percentage on heaps as Extent and logical fragmentation only apply for
indexed tables. Ideally, Scan density is expected to be in excess of 90%. I have noticed that the scan density provided by Showcontig is not always the exact ratio of minimum number of extents/Extent Switches as mentioned in BOL. But definitely a useful parameter to get an idea of fragmentation levels in the tables

Analyze the parameters specified above and if high values are obtained, then execute the query below on the same temp table to get fragmentation values at table level.

SELECT Objectname ,
SUM((Countpages * 8 ))/1024.0 AS Available_Space_in_MB,
CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 AS Space_Used_in_MB ,
(CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 )/( SUM((Countpages * 8 ))/1024.0 ) * 100 AS Percentage_Used ,
( SUM(
CASE
WHEN indexid IN (0,255)
THEN 0
ELSE CONVERT( DECIMAL,CountPages * LogicalFrag)
END /100.0) / SUM(CONVERT(DECIMAL,CountPages) ) * 100.0) [Total_Logical_Fragmentation_in_%], ( SUM(
CASE
WHEN indexid IN (0,255)
THEN 0
ELSE CONVERT( DECIMAL,Extents * ExtentFrag)
END /100.0)/ SUM(CONVERT(DECIMAL,Extents) ) * 100.0) AS [Total_Extent_Fragmentation_in_%],
(CONVERT(DECIMAL,SUM(BestCount)) /CONVERT(DECIMAL,SUM(ActualCount))) * 100.0 AS Scan_Density
FROM #fraglist
WHERE ActualCount!=0
AND Extents !=0
AND CountPages !=0
GROUP BY objectname
--DROP TABLE #fraglist

As mentioned earlier the #fraglist table contains the entire resultset of showcontig and has details like

Row count, Min/Max and Avg Row size, AvgFreeBytes etc. Refer to them while doing a detailed analysis.

Though this script is 2k5 and 2000 compatible, DMVs are definitley better. The major drawback with ShowContig is that it is not an online operation which implies that users can only select and not insert/update/delete when showcontig is executed on a particular table. Also noticed that DMVs are faster as well.

But anyways, this script would be extremly useful if you can spare a few minutes on your maintainence window.

Fill Factor- Part II

Again as another reader requested, I decided to write a fill factor part-2, mainly concentrating on how to set a fill factor. Setting a fill factor is tricky thingy with quite a few factors involved. In this post, let me narrate them and Perhaps come up with a technique to figure out the fill factor percentage to be set.

Setting a fill factor varies from table to table depending upon its usage. Assume that your table has a clustered index on auto identity column then your fill factor can be happily set to 100% as there is almost no chance of having a insert in between as all inserts would happen at the tail of the index. So, it becomes diffcult ( and interesting as well ) only when you have random inserted values on your clustered index key. Let us see how can one go about setting a fill factor in such a case.

The main idea behind setting a fill factor is to keep page splits as minimum as possible. Reducing Page splits can be done by reserving some space for the random inserts that are expected to happen, which is achieved by setting the appropriate fill factor. For setting a fill factor one needs to gather the following details.

1) How frequently one can rebuild the index? (Weekly once/ Daily /). Let me call this frequency to be maintenance window.Maintenance window detail is needed because at every Index rebuild we can get our tables rearranged as per the fill factor.

2) Estimate of how many inserts/updates/deletes is expected with in a maintainence window. Most important is how many random inserts.Other than inserts, updates to varchar,nvarchar columns can cause the pages to split. Its bit difficult at times to find these numbers. But it does matter a lot.

3) Find the Average row size and number of pages in your table( DBCC Showcontig and
sys.dm_db_index_physical_stats can help ) .

After finding the above-mentioned details, one needs to find the Average Random rows to be inserted per page. This can be found by dividing the total number of pages ( showcontig ) by the random inserts estimated. Note that by dividng so we assume that all the random inserts are evenly distributed across the table which in reality may not be the case.So we can do a bit of adjustment to cover the same. Dividing so provides us Random inserts per page.

Total Number of pages/ Estimated random inserts = Random inserts per page.

After that we need to find how much of space needs to be reserved at each page for accomodating random inserts.That would be

Random inserts per page * Avg Row size = Size to be reserved for random inserts.

Fill factor would be

(( 8196 - 96 - Size to be Reserved for random inserts ) / 8196 ) * 100 +/- 5%

96 bytes is the size of page header and hence the adjustment. 5% approximation is meant for the likely skewed distribution across the table for random inserts, varchar updates and deletes if any. Its kind of unusual to have a fill factor below 65%. If your calculations reflect less than 65% then perhaps you may look at shorter maintenance windows(if possible of course) as an option.

After setting the fill factor the responsibility of the DBA is not over. One needs to monitor the Avg page Density and Scan density values ( or Logical fragmentation and Percentage used in my script ) regularly and note the fluctuations and act accordingly.

The story is only half complete. There are more things to be written on the same topic. So will be doing part-3. On Part-3 we would be looking at couple of things.
1) Few more things that are to be monitored after setting the fill factor initially.
2) More often than not one doesnt have a clear idea on number of random inserts expected. In such case what are options we are left with.
So, Watch out for Part-3.

Undocumented sps - Part II

As there was a comment on my last post for more extended undocumented SPs, I thought I will do a part - II for it. I haven’t found a serious necessity till date to use the extended sps that have been listed out here.
But anyways, let me put them here as requested.

1) sp_helpextendedproc :

Lists all extended system procedures including the undocumented ones. The system procedures include extended sps which use DLL ( like xp_cmdshell) and other system procedures which are used internally by SQL Server( whose source code is not available for us to see ). So if you are looking for more extended sps than that are listed here then you may very well run sp_helpextendedproc and it may help you to find the one you wanted.

Example :
EXEC master..sp_helpextendedproc

2) xp_getfiledetails:
Provides file details like size( in bytes ), Creation date and time, Last accessed date and time.xp_getfiledetails works only on SQL 2k and it is removed in SQL 2k5.

Example:

EXEC master..xp_getfiledetails 'C:\test.txt'

3) xp_subdirs:

Provides the list of subdirectories present in the specified directory.Does the search up to only one level.
Example:
EXEC master..xp_subdirs 'C:'

4) xp_dirtree:
Provides the list of subdirectories for the specified directory. Unlike xp_subdirs doesnt limit itself to 1 level and traverses the complete depth.

Example:

EXEC master..xp_dirtree 'C:'

5) xp_getnetname:
Returns the Servers network name. Note that it is different from Serverproperty('Servername') as Serverproperty returns the name of the SQLServer running on the Server. But xp_getnetname returns the network name of the machine running the SQLServer.

Example:
EXEC master..xp_getnetname

6) xp_enum_oledb_providers :
Provides the list of OLEDB Providers.

7) xp_enumdsn: Used to list all DSNs that are created on SQLServer. Works only with SQL 2000.

8) xp_enumerrorlogs : Lists the number of SQL error log files that are present in SQL Server.

9) xp_enumgroups : Lists the number of Windows groups that are present in the SQL Server Machine. This command lists all the groups present that are present in the machine and not just the ones that have access to SQL Server.

10) xp_fileexist - Can be used to find whether a file/directory exists. Indicates whether its a directory/file and whether it has a parent directory.

There are few more like xp_regdeletekey,xp_regdeletevalue,XP_regread, XP_regWrite to meddle with the registry.I seriously dont expect myself to meddle with the registry and would dump it on the System Admin folks to do that :)
So, I am not getting into the details of it.

Useful Undocumented Extended Sps

First post after launching my blog..

Now its lot more exciting ( and a bit scary as well :) ) to post..

Thanks to all the people for reading my blog and please keep reading :)

There are quite a few undocumented extended sps in SQL 2k/2k5. Just thought of discussing couple of useful ones..

EXEC MASTER..Xp_fixeddrives

Returns the free space on all hard disks on the Server running SQL Server.

If you are always running short of space then this one can be handy.

EXEC MASTER..Xp_servicecontrol 'Querystate' , 'SQLAgent'

Returns the Current state of the SQLAgent ie One can check whether the SQL Agent is running or stopped.XP_Servicecontrol requires Sysadmin rights as one can start or stop a sqlagent using it.

EXEC MASTER..Xp_servicecontrol 'Start' , 'SQLAgent' -- to start

EXEC MASTER..Xp_servicecontrol 'Stop' , 'SQLAgent' -- to stop

The second parameter in the above command is the SQL Agent's service name.

For a default instance it is 'SQLAgent'. For a named instance it would differ.

SQLAgent service name can be checked by going to Start->;Run->;Services.msc and identifying Sql agent service. Normally for a named instanced it is named in the following way.

The name would be SQLAgent+$+instancename.

For ex: If the instance name is Raj then its 'SQLAgent$Raj'.

But always you are better off checking it on the actual server.

Also note that Servicecontrol can check the state of other services running on the server.For ex:

EXEC MASTER..Xp_servicecontrol 'Querystate' , 'IISAdmin'

So just be careful while using it or while assigning permissions to it.

Also, this command igoners spaces in between. ie.. If service name is

IIS Admin this one takes it as IISAdmin.

Its undocumented..So no guarentees that it will work the same way after a patch installation etc..So these commands can be used for occasional checking but not recommended to be a part of your application code.So just bear in mind..

These two commands work on both SQL 2k and 2k5 :)