At tonight’s Detroit SQL Server User Group meeting, a question came up about monitoring free drive space without enabling xp_cmdshell. If you need that, here’s an article on monitoring drive space with xp_fixeddrives.
Before I begin, I want to mention that there is a wealth of information on this topic on quest.com including the Don’t Hate - Consolidate and How Do I Know When to Defragment My Database? webcasts, the Tips on SQL Server Consolidation podcast and more.
Consolidation projects should answer the following questions (and this is not intended to be a complete list):
What is the deadline for the consolidation? How much time do you have? This information will drive how you proceed. Ideally, you’ll want a minimum of 3-6 months to determine all the characteristics of both the source and the target environments so you can accurately determine the nuances of all of the environments involved. Information is your best friend here, but be aware that the information is only as good as your ability (read: time and resources) to process it.
What is involved in the consolidation? When you look to consolidate SQL Server environments, you have to bear in mind that you’re dealing with more than just the SQL Server instances and their databases. There are always a number of people and applications that rely on an instance of SQL Server, including websites, internal applications, third-party tools, and existing maintenance plans to name a few. The best way to determine what uses the SQL Server is to set a period of time when that SQL Server environment will be monitored to determine exactly who or what uses the SQL Server that will be moved or migrated before the project is undertaken. You’re going to need time to plan for outages, to schedule migrations and to simply communicate the venture to all of the appropriate parties.
What are the performance characteristics of the environments you’re consolidating? If you consolidate high-traffic environments, its possible that you’ll be creating performance bottlenecks where there weren’t any before. Again, your best defense here is to gather enough data about those environments to ascertain these types of issues. Also, what about the hardware on the consolidation target? Having good, thorough performance data that includes the specs of the hardware you were monitoring is critical in defining the subsystem of the consolidation target environment. And crunching numbers is just the first step here - once you have the hardware configured, you’d be well-advised to stress test that environment, preferably using real databases and real application code. There are some good products in the marketplace from Microsoft, Quest and others that will help you simulate user loads using the application SQL you captured during your monitoring period.
What are the growth trends of the environments? Whenever you deal with a SQL Server database, you’ll inevitably have to deal with database growth trends. Are you still allowing data and log files to auto-grow? This could pose a fatal problem in a consolidated environment where it was just a periodic performance-killer in the past. The 3-6 month monitoring period I suggested should also be used to determine, pretty accurately if you’re allowing that much time, how your databases grow. Whether you use complex algorithms or simple linear plotting, having an idea of your consolidated environments disk storage needs will help you order the right hardware and save a lot of time and hassle with Systems Administrators, Storage Administrators and management in the future.
What kind of maintenance windows will you have once you’ve consolidated? Database backups, index maintenance, ensuring up-to-date statistics, and maybe creating data-marts are activities all DBAs are familiar with. But even though a DBA is familiar with maintenance plans, there is usually some degree of superstition or other “don’t touch it unless something breaks” attitude in every environment. Well, when you’re gearing up to perform a consolidation you’d better get familiar with what’s running and when. Conflicting backups are just one of many issues you could run into. The monitoring you’re performing will tell you when different activities are being performed and should expose when certain additional or different maintenance windows will be available. If you realize you won’t have time for maintenance under the current application and environment settings, you’ll need to adjust your consolidation plan because this is definitely a step you can’t ignore. Missing a backup is something every DBA understands the criticality of, but not maintaining indexes or statistics can ultimately lead to decreased performance, throughput and even to shorter maintenance windows.
In summary, you need to plan, monitor, diagnose, and test before you start consolidating environments. Good communication is important throughout the process, both to ensure that everyone that needs to be in the loop is, in fact aware, but also because this is one of the best ways to expose potential problems before they arise. I hope this helps!
There’s a “new” whitepaper up on Microsoft’s TechNet site (new in quotes because it was recently posted despite being published more than 6 months ago) that goes over SQL Server Pre-deployment I/O Best Practices including how to determine the capacity of your I/O subsystem prior to deploying SQL Server.
A little planning can save a lot of troubleshooting…
Q: If i have a db of size 500 GB and have only one data file of 350 GB, then: 1) would splitting the data file into multiple files (3) on same File Group would help performance? 2) do all 3 files have to be on the same disk and /or same file group?
Kevin Kline says: Generally speaking, the main reason you ever segment a database across files and/or filegroups is to put those segments onto separate physical disks. If you don’t place them on separate disk or RAID arrays, then you get no performance improvement because all of the IO still resides in the same place it did before segmentation.
If, on the other hand, you place the filegroups or files onto separate physical disks you will also be moving the IO to separate disks, thus reducing the overall IO load on the disk subsystem(s).
And of course, the first step here is to make sure that the transaction log file is on a separate physical disk or RAID array before doing any of these other steps. If you haven’t done this step first, you’ll still have terrible IO.
Technorati Tags:
SQL Server, datafiles, file groups, segmentation, RAID, I/O