Latch Waits in SQL Server
Sunday, November 9, 2008 at 9:59PM Many DBA's do not look at or fail to understand Latch wait properly in SQL Server. Latch wait is normally an indicator of poor Disk I/O and should be considered as a lightweight version of a lock. If your are seeing Latch Waits in PerfMon than you should be looking at things like splitting the MDF and increasing the memory. This will improve the transfer of data to from disk to memory or reduce the need to go to disk for data. Pertinent performance counters include Physical disk: disk seconds/read and Physical disk: disk seconds/write and SQLServer Buffer Manager: Page Life Expectancy.
As a DBA you should try and identify disk bottlenecks by using Performance Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN.
Follow these solutions to try and reduce disk waits which lead to Latch Waits
1. Adding additional IO bandwidth,
2. Balancing IO across other drives
3. Reducing IO with appropriate indexing
4. Check for bad query plans
Backing Up to Tape Going Going and Soon to be Gone
Thursday, September 25, 2008 at 10:58AM Take a look at SQL Server books Online you will notice this warning so SQL Server Version 11 will no longer support tape backup.
Take a look at SQL Server books Online you will notice this warning so SQL Server Version 11 will no longer support tape backup.
"Support for tape backup devices will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."
Other Backup Features to Go
PASSWORD = { password | @ password_variable }
Sets the password for the backup set. PASSWORD is a character string.
MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
Sets the password for the media set. MEDIAPASSWORD is a character string.
This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Backups, SQL Server Fill Factor
Tuesday, September 23, 2008 at 12:18AM How many people adjust their fill factor setting from the default my guess is less the 10% and then they complain about slow performance on inserts and updates. You ask them to open perfmon and gather the "Pages split/sec" counters. Low and behold the Pages split/sec" is way up you know where the problem is in there fill factor. The page split has a high overhead on Disk I/0 because it has to reorganize the index and this than leads to index defragmentation have a look at some of my earlier blogs. So you get two problems bad I/O now and poor query performance later as the index is defragmented. So do yourself a favor look at the fill factor set it correctly for each database and then look at each table’s activity and set it individually for each set of indexes on a table.
Rules of Thumb for Fill Factor
- Read Only Table 100%
- Table with minimum modifications over a year 90%
- Table with slight to medium level of changes a year 70 to 90%
- Table undergoing constant updates 50 to 70%
Remember to defrag your indexes regularly and make sure that your size you data files correctly to avoid file level defrag and whilst you at it start looking at ways to collect data about these perfmon counters on a regular basis.
SQL Log Reader
Monday, September 22, 2008 at 11:43PM I see that Kalen Delaney is getting a lot of flak for not supporting some user’s desires for Microsoft to provide a SQL Log reader. Come on people get real there is never really anything gained by attacking a person and really that is so childish I think that it just proves our silly people are. We are meant to be IT professionals, experienced DBA’s so don’t attack people for there opinion. Microsoft offers us the DBA lots of tools that make our life better would a log reader do that for us, maybe in an infrequent situation. I know it’s infrequent because to make our products better at Quest we have a SQL Server log reader in 3 products yep three products. TOAD for SQL Server, LiteSpeed for SQL Server and Change Director. Now I am not supporting illegal use of software but if I was in a bind and really needed a log reader I would go and grab a download from there site and have a free 30 day trial. If I found it useful or it saved my bacon I would most probably pay the price they were asking for it wouldn’t you. So let Microsoft get on with developing a faster and smarter engine that uses less memory that reduces my needs for more hardware the real cost of computing and use the third party software provided by partners to Microsoft.
SQL Server, General, Log Reader Dells New Laptop
Monday, September 22, 2008 at 9:14PM Imagine working on this monster Laptop for Developing SQL Server Databases.
Dell's 17-inch Precision "mobile workstation" rolls out this fall. This portable desktop replacement promises up to 1TB of storage configured in a dual-drive RAID configuration, quad-core processor, and 16GB of graphics memory (1GB for graphics) with enough oomph to power a pair of external 30-inch displays. It also includes a number of unidentified media slots and an integrated jog shuttle controller -- a pretty good hint at the mobile media types this laptop is targeting.
I hope Quest is kind to me this xmas and buys it to replace my old Lenovo T60P
