Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

SQL Server - Have you observed DBCC SHRINKFILE operation performance, on huge databases? - SQL Server Knowledge Sharing Network (@sqlmaster)

SQL Server - Have you observed DBCC SHRINKFILE operation performance, on huge databases?

In general it is not a best practice to perform SHRINK database operation on a production server, atleast regularly!

Sometimes it may be compulsory to keep them sized in order to ensure the disk storage is not compromised for any sudden changes to databases ETL processes, coming to the point by design the DBCC SHRINKFILE operation is a single-threaded operation that means you cannot define or configure the server to use multiple CPUs or a dedicated CPU. So troubleshooting the performance problems on a database system is very tricky, more important is where to look for a problem and for instance it is frustating to see why system reacts in such a bad way even for a simple query execution and this is where you need to look at how CPU, Memory & disks are performing during this operation

Coming to the subject the referred DBCC operation needs to perform the exercise of moving database pages from tail of the file to the beginning in order on the data file, with one page at a time though. SO you need to be careful to select this operation on a huge database as it will tend get the server down to its knees for a single simple-query execution, also the SHRINKFILE operation often make it defragmentation from bad to worse and in many situations I have seen this increases the file logical fragmentation whereby you will see huge difference in performance to produce few hundreds of rows result set.

If you see such performance issue then look at from a high level, there are two paths to identifying CPU performance problems. The first is reviewing the system's hardware performance, an exercise that helps determine where to look when you head down the second path, reviewing the server's query efficiency. This second path is usually more effective in identifying SQL Server performance issues. Unless you know exactly where your query performance issues lie, however, you should always start with a system performance evaluation that will lead to go on both of these routes. On such SQL Server instances where the relational engine actually handles its own extremely efficient queuing and threading to the Operating Sysstem and having CPUs with hyper-threading is a common scenario, this will have the affect to overload the physical CPUs on systems with already high CPU utilization. This is where the threads operation converted as queues from SQL Server with multiple requests to perform work on multiple schedulers. This is where the Operating System struggles to cope up to switch the context of the threads back and forth on the physical processors to satisfy the requests that are being made even if the two logical processors are sitting on top of the same physical processor.

Taking back to the SHRINKFILE operation on the database table(s) with a clustered index(es) on a huge table (rows) then you will see much degraded performance because of the heaps and those heaps have many non-clustered indexes, where it is different to the  clustered index situation. In this case the SHRINK operation of moving the pages having with IMAGE data or Large Object Blob will be too slow, as it has to read the data from each page to arrange. Further the most content of an index/table resides at the end of the file, you can rebuild the indexes to move them to the front end of the file. Also bear in mind to keep a close watch of Transaction log space on the databases in addition to the TEMPDB which is used extensively in SQL Server 2005 version.

So by the end you should ensure to schedule the SHRINK operation during the less traffic hours and when server consists with multipe CPUs to take advantage of performance to finish the operation in timely manner.

 

Published Thursday, May 8, 2008 9:35 AM by ssqa.net

Comments

# http://sql-server-consultancy.co.uk/database-news-sql-news.html

Tuesday, May 13, 2008 2:22 PM by TrackBack

# http://sql-server-consultancy.co.uk/database-news-sql-news.html

Tuesday, May 13, 2008 2:22 PM by TrackBack