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.

You may be aware that or seen within your Database platform about slower disk performance than expect having multiple disks in Windows Server 2003 environment when you use a hardware-based redundant array of independent disks (RAID) or a software-based RAID. This could be due to one of the reasons such as starting location of partition is not aligned properly with a stripe unit boundary in the disk partition that is created on that RAID. Further information we can refer from one of the KBA929491 article that:

A volume cluster may be created over a stripe unit boundary instead of next to the stripe unit boundary. This is because Windows uses a factor of 512 bytes to create volume clusters. This behavior causes a misaligned partition. Two disk groups are accessed when a single volume cluster is updated on a misaligned partition.

Windows creates partitions that are based on a predefined number of sectors. The starting location for a disk partition in Windows Server 2003 is either the 32nd or the 64th sector, depending on the information that is presented to the operating system by the mass storage controller.

It is the default configuration from the disk partitions that they reserve the first sector for code and number of sectors with the starting sector information, thats how the next sector will consists the information about data part. In this regard Microsoft recommends to use DiskPart.Exe tool which can be used to create disk partition and specify the starting offset, before that it is recommended to findout what is the current size set of partitioned disks and you can get more information about the Diskpart.exe tool from this DiskPart-TechnetArticle and Exchange-Server_IO_Alignment article links.

Within the recent project tasks I have proposed to use disk partition as 64K blocks for the disks that are used for transaction logs, as there is no doubt it enhances the performance when writing the huge chunk of logs for a database (if required). Also I have discussed with Microsoft Development team about the recommendations they use internally with 64K for both data & log, further I suggest to refer Bob Dorr's explanation on how best system can make use of it during backup/restores link. One of my colleague (Ken) has raised the concern that "I can see that sometimes it would help but not always. My *guess* is that SQL won't always have 64k of data that needs to be written urgently (e.g. after a COMMIT). I guess it all depends on the algorithm's used and I've not seen them described in detail anywhere".

Further I have found that in the past when the Infrastructure team raised a query with Microsoft team it has been discourage to set 64K blocks to the drives other than Data disk. The usual disk layout seperation we follow for the SQL Server setup as follows: 

 In continuaton to UK User Group conference (2 day) in Microsoft Campus, Thames Valley Park Reading here is my presentation to download that was delivered on 09th April 2008. 

If you are unable to download from SkyDrive then click here.

During that conference I have represented as a member from, UK SQL Server User Group & Scottish Area SQL Server User group and I believe this is an unique opportunity to promote your skills & learn something new in technology.

Ok here is the hot news for SQL 2005 users!!!

From the Are you ready for SP3 post  here, it has been officially announced the SP3 for SQL Server 2005 will be available in CY2008 (calender year 2008) that means before 31st December 2008!

More from this DataInsider blog post.

Here is another candidate of 7 within Cumulative Update service for SQL Server 2005 SP2.

SQL Server Release Services team has announced (yesterday) that another instalment of Cumulative Update (series here) for SQL Server 2005 on Service Pack 2, it is not easy decision for User Community to apply with CU than going Service Pack release which is more stable as it proved from previous versions.

So the golden rule is always test this CU7 and to obtain the usual route (since CU5) was raising a online request (as referred on KBA949095):

..... it is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems. This cumulative update package may receive additional testing. Therefore, if you are not severely affected by any of these problems, we recommend that you wait for the next SQL Server 2005 service pack that contains the hotfixes in this cumulative update package.

To resolve this problem, submit a request to Microsoft Online Customer Services to obtain the cumulative update package. To submit an online request to obtain the cumulative update package, visit the following Microsoft Web site:

http://go.microsoft.com/?linkid=6294451

In addition to this if you need to know about previous CU releases refer to KBAs below (that was articulated on the above KBA): 

CU#6 KB Article: 

CU#5 KB Article: 

CU#4 KB Article: 

CU#3 KB Article:

CU#2 KB Article:

CU#1 KB Article:

CumulativeBlog subject posts.

Again the best advice is to refer the above KBAs and download it if you are affected by the speciified problem within those articles, though these are public announced one but not with available for free, overall it is best to test it before applying on live instance.

You may be aware that using normal method of http://servername/reports will give access to the users and if you grant reporting service permissions using the folder properties security (or report manager) to the Windows groups you might need to restart the IIS in order to take the new groups affect.

 In this case it may not be possible to restart IIS services on a production server unless the user activity is low or arranged downtime is agreed, so how to resolve this issue. The default IIS configuration has delay of 15 minutes before users tokens are updated. For example, if you change the password on a user account, you will be able to connect to the server with both the old password and the new password.  So either you have to wait for 15 minutes or change this interval by referring following method that was referred in one of the MS Knowledge base article (I don't remember the number)

The subject say it all, as of today the mainstream support for SQL Server 2000 (SP4) and SQL Server 2005 (SP1) will be ended. That means for SQL Server 2005 service pack 1 there will not be any support and that can be obtained with a Custom Support agreement where few applications may have issues in upgrading to Service Pack 2 for SQL Server 2005, also the general suggestion is to upgrade to Service Pack 2 on SQL 2005 that has been stable since 1 year from its release!

Further going towards the major implementation on global basis,many enterprises out there are sitll functioning with SQL Server 2000 version (SP4 for discussion sake) and this clarifies a bit further that technical support continues till 2013 (hopefull until April 9th) and the mainstream support for any kind of hotfix or cumulative fix will be ending from today, the usual reply you would get from a Support team is to upgrade the SQL installation to SQL Server 2005 SP2 (atleast) and if you have any extended support agreement with CSS team.

This brings a big question how about Service Pack 2 for SQL Server 2005, the usual Product Lifecycle page on Microsoft confirms that such support ends either 12 or 24 months after the next service pack releases or at the end of the product's support lifecycle or whichever comes first.  You can visit that page for more information on specified products.

Transactions and Checkpoints are important factors for data consistency, when it comes for ETL processes that too on SSIS what is your stand? 

I'm covering this important task for the sake of newbie & junior DBAs, also the similar question was raised by the users during a user group meeting. SQL Server Integration Services has this sophisticated method in evolving a failed package to execute from the point of failure, instead of rerunning the whole package. If you ever developed sophisticated DTS package, and there is another error in one of the task, usually you have to (re)execute the package from beginning. Making this point of execution for the complex packages, is a real problem  and having big data that get involved in ETL process, it will consume considerably amount of time wasted. This is where the CHECKPOINT process kicks in SSIS services where you have option not to start package from beginning in case the package gets error.

Similar to this point you have to ensure the packages are configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time the package is run. The package execution is logged to a file, when the task in a package gets failure to execute. When you correct the task, and re-execute the package, the beginning of execution will start at the failed task not the first task of a package. The package will read execution in the file, and resume executing the task and the rest. Once it is successfully executed, the package will remove the file. Nice feature that help you a lot when you develop a complex calculation package. There is another downtrend using this checkpoint and transactions method, in the same package. This  could cause unexpected results, for instance when a package fails and restarts from a checkpoint, the package might repeat a transaction that has already been successfully committed.

Tip: To use this within the migrated packages from SSIS   using dtexec utility, you can opt to run /CheckPointing on option of dtexec is equivalent to setting the SaveCheckpoints property of the package to True, and the CheckpointUsage property to Always.

To use this approach in SSIS you can use BIDS (BI Development Studio) by enabling checkpointing of a package, where you can define package’s properties in Checkpoints section with a  'Checkpointfilename', set CheckPointUsage to 'IfExists' or 'always', and set to true for SaveCheckpoint. Then you set FailPackageOnFailure property of a task to true, otherwise the package will not create checkpoint file if it fails. To see whether it works or not you can create a package with  sample execute SQL Task package, set second package property name ForceExecutionResult to failure, and run the package. If you notice the output window, package status will have failure status, then you will find checkpoint file that you specify on Checkpointfilename property. Secondly you can perform the correct task, by keeping 'set ForceExecutionResult' property of second task from 'Failure to success'. Run the package again, this time you notice that package will be executed from the second task (failed task) rather than first task. Once it is successfully executed, the checkpoint file will no longer exist, because the package deletes the checkpoint file that no longer useful.

In addition to this you have to be aware about types of transactions, such as Distributed Transactions and Native Transaction (SQL Engine). Both of these types are suppoted where the initial one uses the 2 phase commit type with MSDTC service, so you have to ensure that DTC service is up and running in order to execute the package successfully. On top of this within the package you need not specify BEGIN TRAN.... COMMIT/ROLLBACK... ENDTRAN statements, as they are taken care by DTC service itself. Secondly the native approach of Transaction commit/rollback method by SQL engine you have to use the explicit specification of BEGIN TRAN... END TRAN statements under 'Execute SQLTask' component of SSIS, also ensure to setup the connection manager to keep the 'RetainSameConnection' property to TRUE. 

To wrapup you can take more help from local documentation of SQL Server aka BOL, that too updated one. How-To topics in BOL refers:

To configure a package to restart

MVP Global Summit, what it meant for MVPs?

At one place, global connections with peers with a shared insight with a flavour of engagement with relevant product manager, sounds terrific isn't it!

Who can attend - as per the previous post on MVP Global Summit 2008 here, if you are a Most Valuable Professional or Regional Director then you are invited to attend this exclusive summit between April 14 - 17 2008. The venue is hosted hosted at the Washington Trade and Convention Center in Seattle and at Microsoft headquarters in Redmond, Washington, with an exclusive access to more than 400 sessions and panel discussions and a range of networking opportunities, as well as Microsoft top executives such as Steve Ballmer, chief executive officer, and Ray Ozzie, chief software architect.

So what's new with this post, fellow MVP John OBrien (Live Development) has done an excellent & another valueable job by teaming up with Summit organisers to produce a 'small world' Virtul Earth visualisation of where all the attendees of the summit are coming from. As you can see the data is a general area (city basis) to zoom and if you see larger dots that represents more MVPs on that area, pretty cool!.

This summit may be first one for the new MVPs who are awarded since last July 2007 cycle and for me it is 2nd one, its worth a trip to seattle with a deep dive to immerse into technology.

Another part of Technet webcast series on SQL Server 2008. 

In this part a focus on IT professionals and the SQL Server 2008 the Trusted Platform. In the first part of the series we spent time looking at how Contoso was using SQL Server 2008 as a productivity platform. Now we change direction from developers and look at how Contoso's IT operations embraced the new and enhanced features to make sure that SQL Server 2008 was a trusted platform. The first two parts address security, which is a main theme for Contoso as they have large amounts of critical data that needs to be protected against both internal and external threats. Either data loss or compromise would be a nightmare scenario. In this session, we look at features such as surface area configuration policies, external key management, and Network Access Protection (NAP), and Windows BitLocker Drive Encryption, which Contoso could use to protect its data.

When: Monday, March 31, 2008

Time: 11:30 A.M.–1:00 P.M. Pacific Time

Event ID    

Register Online

Flood of questions fromthe users during a recent usergroup meeting.

• What will the specification for the SQL Server 2008 Express version be? 
• What will be the differences between it and the 2005 version, not with MSDE? 
• How about SQL Agent attachment to the SQL Express engine? 
• Any memory limitations and CPU limit?
   

I would like to call this as how efficiently you can obtain counting rows in SSIS package. Usual thought would come is @@rowcount or  COUNT_BIG functions, but this is not that efficient way to obtain the results.

SSIS provides the snippet within its components such as using Row Count component, for this you have to create a variable of integer typeat a scope where you can see it from your Data Flow task. Then add this RowCount component to the Data Flow task place where you want to get results. Not yet finished once it is specified in that task you have to edit the Row Count component and set its VariableName property to the name of the variable you created.

The downside of this method is this component is not updated quickly unless the complete DataFlow task is completed, may be we can use VBScript component to get upto date count values and again this is not possible always to embed within the package.

So going deep into the requirement such getting rowcount values on all sorts of level such as, how many rows were inserted to a destination? As usual you can achieve this by using RowCount component just before the destination component. A little glitch here is missing of failed rows during that insert operation, because it would not take into account rows which failed to be inserted. So there are two patterns you could use for OLEDB destinations:

• Use an ExecuteSQL task to count rows before and after the Data Flow has executed, and compare;
• Add a RowCount component before the destination, and one on the error output of the destination, and compare the values of the two variables after the Data Flow has completed.