Tuning SQL Server performance via disk arrays and disk partitioning

One of my collections, about MSSQL Server Tunning. Enjoy it...

Author : Denny Cherry, 08.19.2008

As a DBA, much of your focus is on performance tuning SQL Server. But have you spent time to tune the hardware supporting your SQL Server system? Are you using the optimal disk array configuration? Are the disk partitions aligned? This tip discusses how to get your SQL Server hardware performance in top shape – whether the system is already in operation or it's a new setup.

With the massive amount of raw horse power available in today's server class hardware, it's easy to skip over the hardware when it comes to performance tuning the SQL Server database. After all, with so much power available, who cares if something takes a few extra milliseconds to complete? Is anyone really going to notice that extra millisecond?

But what happens when you perform an operation that takes 10 extra milliseconds to complete; and it needs to perform 100 times an hour, for a year? All of a sudden, that 10 milliseconds turns into 2.4 hours. If you perform that operation 1,000 times an hour -- which isn't all that unheard of in a smaller OLTP database -- you are now looking at more than 24 hours of wasted time.

In my particular environment, we run the same stored procedure at least 2,000 times per minute. If that stored procedure takes an extra 10 milliseconds to complete, we are looking at eight hours of lost time daily, or 121 days of lost time per year.

Tune SQL Server by tuning disk arrays

There are a few places to check hardware components when tuning your SQL Server system. The easiest components to check are disk arrays. Typically, disk arrays are where the most time is expended

More on tuning SQL Server performance and hardware: Determining SQL Server database storage requirements Optimize disk configuration in SQL Server Storage area network (SAN) basics every DBA must know

waiting for something to happen. There are a couple of ways to tune the disks to improve SQL Server performance. The first is to make sure your disk array has enough spindles to handle the workload that will be placed on it. Second, make sure the disk arrays are in the correct RAID level to offer the best support level for the database.

While it is true that RAID 10 offers better write performance, in most cases, RAID 10 isn't required for the data files. That said, you should use RAID 10 for your transaction logs and tempdb database, as they are mostly write files. The reason I say not to use RAID 10 for all database files is that RAID 10 is very costly to implement in large disk sizes. This is because for each spindle used for data, a second spindle is used for redundancy.

Finding out if you need more spindles on an existing system is easy. Open Performance Monitor on the server and add the "Physical Disk" object and the "Current Disk Queue Length" counter. Some queuing is OK; however, there is a tipping point. To find out where the tipping point of "OK queuing" and "too much queuing" is, take the number of disks in the array and multiply it by two. If the result is greater than the maximum value in Performance Monitor, then you have too much queuing. When we talk about the number of disks, we're referring to the number of disks that are actively working with data. If you have a RAID 10 array, this is half the number of disks in the array.

"Number of Disks" x 2 = Maximum Allowable Queuing

How to configure the disk array on your new SQL Server system

When working on a new system without any load on it, making sure you configure your disk array correctly is a little more challenging. If you have another system with the same amount of load on it, you can use that system as a guide. However, if this is the first large system in your environment, then getting it correct can be a bit harder.

You'll need to look at your database system and the expected transactions per second, and make an educated guess on how many spindles you'll need. When dealing with high-end drives, expect each drive to give you about 100 IOPs to 120 IOPs per second per disk in an OLTP environment. When dealing with SATA drives, expect each drive to give you about 60 IOPs to 80 IOPs per second per disk in an OLTP environment. Those numbers will go up when working in an OLAP environment because OLAP databases put a different kind of load on the disk system. It's a more sequential load, whereas OLTP databases put a random load on the disks.

Disk partition alignment improves SQL Server performance

Once you set up your disk array, you'll want to make sure the partition you create is correctly aligned. By default, when Windows (or any other operating system for that matter) creates the partition on a disk or array, the partition is not correctly aligned for peak performance. Disk drives are made up of 1K blocks. The physical disks like to do all their operations in 64-block chunks called clusters. Conveniently, SQL Server likes to do all its operations in 64 K operations -- there are eight 8K blocks in each extent, and SQL does its reads one extent at a time. When the partition is created, the boot sector is created at the beginning of the partition. This boot sector is 32 K in size, causing the 64K operations to be spread between two 64K clusters. This then causes each logical operation to take twice as many physical operations as needed.

You can see your current alignment offset by using the diskpart application. Open a command prompt and run diskpart.exe. When you are prompted with a DISKPART> prompt, type SELECT DISK n where n is the disk number you want to look at -- the command LIST DISK will give you a list of disks in the machine. After selecting the disk, type in "LIST PARTITION" to get the partition information, including the offset.

Figure 1: Run the DISKPART application to view disk alignment.

In order to create the partition, you'll need to use the CREATE PARTITION command. The full command is CREATE PARTITION PRIMARY ALIGN=64. This creates the new partition with the 64K offset, aligning the partition into the optimum position for maximum performance.

Check out part two in this tip series, configuring memory and CPU processing for improved SQL Server performance.

Tuning SQL Server performance via memory and CPU processing

After a long journey browsing on internet, finally I found this interesting and usefull article for tunning our MSSQL Database Server. So in this place i re posting to share with you guys.... enjoy it ;)

Author : Denny Cherry, 09.09.2008
In my previous tip, Tuning SQL Server performance via disk arrays and disk partitioning, we talked about how important it is to ensure that your storage was set up correctly to optimize SQL Server performance. However, storage isn't the only part of SQL Server hardware that needs special consideration when designing your infrastructure.

SQL Server memory can also impact performance. While having too much memory in a SQL Server system is a waste of money, having too little memory is extremely detrimental to performance. Unfortunately, determining when you need more memory in the system can be a bit tricky. When memory problems begin, you'll start to see an increase in disk I/O, as well as an increase in disk queuing. You'll also see a decrease in the buffer cache hit ratio and page life expectancy. As memory requirements increase, you may begin to see these error messages in the log file:

A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: %n seconds. Working set (KB): %w, committed (KB): %c, memory utilization: %u.

SQL Server has encountered %o occurrence(s) of IO requests taking longer than 15 seconds to complete on file [%f] in database [%d] (%i). The OS file handle is %h. The offset of the latest long IO is: %l.

Unfortunately, this is not the only time these errors are reported, so you have to use them along with the performance monitor metrics to determine that memory is actually low.

When dealing with SQL Server memory issues, there are a few options to resolve the problems. The easiest solution is to increase server memory, which increases the amount of buffer

More on improving SQL Server performance: Memory configurations for procedure cache and buffer cache Configuring SQL Server memory settings Clustered and non-clustered indexes in SQL Server

cache available. This adds to the amount of data in memory and reduces your disk I/O. Other potential solutions include removing clustered indexes for extremely large tables and using only nonclustered indexes for the table, including the Primary Key.

This will only make a difference when the clustered index is being used for lookups, and clustered index seeks are used. If another index is in use, it will not relieve any memory pressure, as the clustered index won't be in memory. If you're using clustered index scans, then this turns into table scans that load the table into memory instead of the index. If clustered index scans are being performed, then a new nonclustered index may help the situation without removing the index.

How to monitor CPU queuing

The CPU is another piece of hardware that can cause potential performance problems. Most people only look at the speed of or number of CPUs. However, just like disks, CPUs can become bottlenecked. If there is a CPU bottleneck, you may not even see the CPU performance at 100%. CPUs have command queues in much the same way that disks have I/O queues. Commands are loaded into a CPU queue and the operation waits for the CPU to become available before performing the operation. As CPUs became faster, we could do things much faster within the CPU, but we could still only do the same number of things at one time. Now, as dual-core, tri-core and quad-core CPUs become available, we can process more commands at one time.

You can monitor your CPU queue using SQL Server Performance Monitor. You'll find PerfMon under the System object, with the counter name "Processor Queue Length." Pretty much any queue length other than zero indicates a need to increase the number of operations that SQL Server can perform at any one time. It doesn't indicate a need for faster CPUs, but a need for more CPU cores. Today's newest servers support 32 cores per server, and some of the most advanced servers support up to 64 cores -- when chases are scaled together support for 64 cores can be built (available only from certain vendors).

In parts one and two, I've pointed out a variety of places within the hardware that impact whether your SQL Server system will run at peak performance. These tips are not the be-all, end-all solutions to performance problems. Table design and index tuning always have been and will continue to be extremely important. Today's SQL Server is expected to do more work for more hours of the day, which makes hardware tuning more important to the success of the database platform. With these tools in your arsenal to combat performance problems, you'll be able to get every ounce of performance from the existing hardware with no or minimal hardware upgrades to the platform. But when you do need to make those purchasing decisions, use these tips to make the correct purchasing decisions to get the most upgrade for your dollars spent.

Using Performance Monitor

As you probably already know, SQL Server is very good at tuning itself. It has the ability to monitor itself, and through a feedback loop, it knows how to internally adjust and tune itself so that it keeps running efficiently, even when external events, such as the number of user connections or the amount of available RAM, change over time.

But as we all know, SQL Server's ability to self-tune is not perfect and does not take into consideration every possible aspect that affects its performance. As a DBA, we need to help SQL Server along, providing it the resources it needs for it to do a good job serving up data.

As a good DBA, we don't want to find out from our users that SQL Server is having a performance problem. Instead, we want to be proactive and catch performance problems before they arise. That is what Window's Performance Monitor can help us do. It is a tool that allows us to monitor what is going on with our SQL Server, and to provide us the information we need to make decisions on how to best tune our SQL Servers.

Performance Monitor is an important tool, because it not only provides us with information on how SQL Server is performing, but it also lets us know how Windows Server is doing, which of course directly affects SQL Server's performance. [6.5, 7.0, 2000] Updated 7-10-2006

The "Performance Monitor" under the "Microsoft SQL Server" entry under your Start Menu is the same "Performance Monitor" under the "Administrative Tools" entry under your Start Menu. They are the same programs. What is different is that when you bring up Performance Monitor from under the "Microsoft SQL Server" entry, is that it comes up already running several pre-configured SQL Server performance counters.

The Performance Monitor under the "Administrative Tools" entry does not come with any pre-configured counters loaded. Personally, I dislike the "Microsoft SQL Server" option and always choose the Performance Monitor option under "Administrative Tools." This way, I always get to choose the SQL Server Performance Monitor counters I prefer to use. [6.5, 7.0] Updated 7-10-2006

*****

If you are like me, you have one or two SQL Server production servers that are very important to monitor. To help me keep tabs on these "high-visibility" SQL Servers, I always run an instance of Performance Monitor in the background on my Windows NT 4.0 or Windows 2000 Workstation desktop pointing to these servers. I don't log this data, but I like the ability to very quickly take a look at key performance counters (in chart mode) throughout the day.

Since Performance Monitor is always running, I don't have any excuse not to take a peek at my SQL Server's performance at various times throughout the day. You would be surprised at the things you find, including bottlenecks you may not know you had. In addition, after some time, you begin to better learn how your server's perform, which makes it easier to diagnose potential problems as they arise.

In order to minimize the affect of this constant monitoring on your SQL Servers, you will not want to monitor too many counters. Here are the key counters I like to watch on a regular basis:

• Memory — Pages/Sec: To see how much paging my server is doing. This should be close to zero on a dedicated SQL Server. You will see spikes during backups and restores, but this is normal.
• Network Interface — Bytes Total/sec: To see how much network activity is going on.
• PhysicalDisk — % Disk Time — _Total: To see how busy all the disk drives are.
• PhysicalDisk — Current Disk Queue Length — _Total: Also to see how busy the drives are.
• System — % Total Processor Time: To see how busy all the CPUs are as a whole.
• System — Processor Queue Length: Also see how busy the CPUs are.
• SQLServer: General Statistics — User Connections: To see how many connections (and users) are using the server. Keep in mind that one connection does not equal one user. A single user can have more than one connection, and a single connection can have more than one user.
• SQLServer: Access Methods — Page Splits/sec: Lets me know if page splits are an issue or not. If so, then that means I need either to increase the fill factor of my indexes, or to rebuild the indexes more often.
• SQLServer: Buffer Manager — Buffer Cache Hit Ratio: To find out if I have enough memory in the server. Keep in mind that this ratio is based on the average of the buffer hit cache ratio since the SQL Server service was last restarted, and is not a reflection of the current buffer cache hit ratio.
• SQLServer: Memory Manager — Target Server Memory (KB): To see how much memory SQL Server wants. If this is the same as the SQLServer: Memory Manager — Total Server Memory (KB) counter, then I know that SQL Server has all the memory that it wants.
• SQLServer: Memory Manager — Total Server Memory (KB): To see how much memory SQL Server actual is using. If this is the same as SQLServer: Memory Manager — Target Server Memory (KB), then I know that SQL Server has all the memory that it wants. But if this is smaller, then SQL Server needs more available memory in order to run at its optimum performance.

Based on my experiences and preferences, these are the counters I like to watch regularly. If I see something interesting in these counters, I often add additional counters as necessary to get a more detailed look at what is going on. I run Performance Monitor from my desktop, not the server I am monitoring in order to minimize overhead on the SQL Server.

By default, readings will appear every second and less than two minutes at a time will appear on the graph. I don't find this time frame all that useful, so I change it to 36 seconds, which displays an hour on the screen of activity. This gives me a good feel of the health of my critical SQL Servers without putting any undue overhead on the server.

If you don't already check your SQL Server's key performance counters throughout the day, you need to start this important habit. The more you learn about how your servers run, the better DBA you will be. [6.5, 7.0, 2000] Updated 7-10-2006

*****

Once you have identified the Performance Monitor counters you like to use, you can save them in a file and then later reload them when you want to see them again. This way, you won't have to re-add the counters to Performance Monitor each time you use it. In fact, you can create different sets of counters, with different names, so you can track different types of counters at a time. In addition, each different type of Performance Monitor's modes, such as "Chart" and "Log," allows you to store its own set of counters.

How you use Performance Monitor to do this depends on if you are using Windows NT 4.0 or Windows 2000.

If you are using Windows NT 4.0, then use Performance Monitor's "File" menu option to save and load your counter files.

If you are using SQL Server 2000, then you will use the "Console" menu option save and load counter files. [6.5, 7.0, 2000] Updated 7-10-2006

*****

When monitoring your server using NT Server 4.0's "Performance Monitor," or Windows 2000's "System Monitor" tool, keep in mind that the more counters you monitor the more overhead that is required to perform the monitoring. This is true whether you are viewing a performance chart, logging counters, or creating alerts based on counters. Because of this, don't monitor counters you don't need to monitor. If you are using multiple counters for your monitoring, but soon realize that one or more of them are of little value to the task at hand, then remove these counters from your monitoring.

One difference between NT Server 4.0's Performance Monitor and Windows 2000's System Monitor is how logging is done. In Performance Monitor, you must log entire performance objects, you can't just log individual counters. This can lead to very large log files with a lot of data you don't need. In System Monitor, you are now able to log individual counters, not just entire objects. This makes these logs much smaller in size, making it more practical to log over long periods of time. [6.5, 7.0, 2000] Updated 7-10-2006

*****

When monitoring your server using NT Server 4.0's "Performance Monitor," or Windows 2000's "Performance" tool, keep in mind that how often you set these tools to collect counter data affects the amount of overhead experienced by your server. For example, the default counter collection interval for displaying near real-time charts of performance counters for these two tools is 1 second. If you increase this to .5 seconds, then overhead is essentially doubled. But if you decrease it to 5 seconds, then overhead is substantially reduced).

So how often should you collect performance counter data? It depends on what your goals are. In some cases, you need to collect data in near real-time (every second), and it other cases, collecting it every 5, 15, 30 seconds is adequate. The key is to not collect data more often than you really need to. Personally, when I collect performance counter data to display as a chart, I use 36-second intervals. Experience has proven to me that this time interval best meets my needs for monitor SQL Server's performance, and it also allows exactly one hour of chart data to be on the screen at one time. Of course, your mileage may vary. [6.5, 7.0, 2000] Updated 7-10-2006

*****

When monitoring a SQL Server using Performance Monitor, don't run Performance Monitor on the same server you are monitoring. Instead, run it on a different server or workstation and remotely monitor the SQL Server. Running Performance Monitor on the same server you are monitoring will skew the results.

Along the same train of thought, don't run both Performance Monitor and Profiler at the same time, even if you are running them remotely. This is too much overhead and will cause your SQL Server to suffer some performance degradation. In addition, running both together can cause Performance Monitor to produce less than accurate data because of the overhead of Profiler running at the same time. [6.5, 7.0, 2000]

SQL Server Tuning

Q : I have SQL Server 2000 with approximately 45 databases on a single instance. These databases are used by multiple ASP applications running on my public Web site. SQL Server regularly reaches 100 percent CPU utilization and requires reboots. How should I start troubleshooting and repairing this problem?

A : Start with rebuilding all of your indexes. It’s easy, assuming you have memory configured correctly. Then run SQL Profiler for a few hours and capture a snapshot of your standard business T-SQL traffic. Run this trace through the Index Tuning Wizard (ITW) that comes built into SQL Server 2000. It will take a look at the queries and make recommendations for indexes to improve performance.

If you have SQL Server 2005 installed, you can use the Database Tuning Advisor (DTA) which is the successor to ITW, to tune your workload. DTA can tune SQL Server 2000 as well. It tunes a database on a production server by offloading most of the tuning load onto a test server. DTA uses the production server hardware configuration information, without actually copying the data from the production server to the test server. It only copies the metadata and necessary statistics.

Finding the Bottleneck

Q : When I run one particular query, CPU usage will reach 100 percent after a few seconds, and the server will be shut down in a few minutes. I can reproduce it every time. We have hundreds of millions of unique values in the table in question.

Is this a hardware issue or a software issue? I can’t get any information from Event Log. The server is an HP AMD 64-bit machine. I am using SQL Server 2005 (64-bit).

A : If the system really is crashing (bugchecking) then you will need to take a look in the memory.dmp file. First check the Startup and Recovery settings (in the Control Panel) and make sure it’s set to create at least a kernel memory dump. If that is already set, then find the memory.dmp file and save it to another machine. The next step is to download the Debugging Tools for Windows (windbg) from microsoft.com/whdc/devtools/debugging. Make sure to select the right package for your system (in this case you want the 64-bit versions), and then choose the x64 package.

When the debugger is installed, run windbg and set the symbol path to the Microsoft Symbol Server. To do this, type Ctrl+S and enter a valid symbol path. I usually use something like srv*c:\symcache*http://msdl.microsoft.com/download/symbols. For more information on this, see Debugging Tools and Symbols: Getting Started.

Next, open the memory.dmp file in your favorite Windows debugger and run !analyze -v. This will tell you what the bugcheck was and give more details on why the server crashed.

Log Shipping or Mirroring?

Q : Is it true that once mirroring becomes available, there is no reason to use log shipping anymore? It seems that mirroring is less functional than log shipping if you want to use the mirrored database for reporting. It also seems that mirroring only allows you to query against a database snapshot, which represents a point in time. Log shipping, on the other hand, appears to allow you to query against current data. I know the data can be out of date depending on how often you ship the logs, but this seems better than having to constantly create new snapshots. In other words, shipping the logs every hour seems a lot better than using mirroring and creating a new snapshot each hour.



A : If reporting is needed, log shipping is not necessarily better in this scenario than mirroring and using snapshots. The log shipping secondary must be restored with the WITH STANDBY option in order to allow read-only access. The data is static at that point in time up to the last transaction log loaded, just like a database snapshot represents a point in time. If you need more up-to-date data to query against with log shipping, the secondary database must have transaction logs applied, which means users cannot access the database while that is occurring. Log shipping is not really a reporting solution.

Database mirroring and using a snapshot on the mirror may be easier to administer since you are not affecting the process like you are with log shipping. You are just creating a new snapshot on a periodic basis. The problem, though, with mirroring and snapshots is that since each snapshot is named, either each new snapshot created would have a different name (which may impact the reporting application) or the old snapshot would need to be deleted and then created again (which would impact the availability of the reporting solution). So to some degree, both log shipping and mirroring have different advantages as reporting solutions in addition to their intended use for availability and disaster recovery. But with log shipping, reporting is unavailable when you apply a new transaction log to refresh data, and with database mirroring, reporting is unavailable when you drop and recreate the existing snapshot, or create an additional snapshot to make it more current.

Log shipping is based on backup and restore, and supports multiple secondaries, each of which can have its own schedule or delay, which can also account for other problems, such as user error. Database mirroring can be simpler to implement. When using its synchronous mode, the mirror database is more up-to-date (with no data loss) than its log shipping counterpart, which is only as good as the last log that has been applied to the secondary. With the right configuration, mirroring also supports an automatic failover scenario. Database mirroring, unlike log shipping, can only have one mirror database. Both database mirroring and log shipping provide protection at the database level, while failover clustering provides protection at the instance level. A geographically dispersed cluster could work over distances for availability purposes, but this can be an expensive solution. Database mirroring and log shipping can work with clustering to provide extremely high availability for your SQL Server™ instances.