July 6, 2006 at 9:16 am
Hi all,
I have to visit a branch of the company that I work for.
The have a SQL server 2000 box there and they ask me to check the box... (first time I have this request)
Where should I start?
Does anyone have a method? steps?
Any book or articles to check?
Tks in advance
Johnny
July 7, 2006 at 3:51 am
Hi Johnny,
You need to look into running traces in SQL Server Profiler. A trace is basically a record of all the SQL being run on the server together with performance information. Depending on your load, there are options that let you filter down to just the longest running queries, so you can quickly identify bottlenecks, and what is taking up the most resources. There is plenty of information in SQL Server Books Online.
Once you have done that you can examine the execution plan of the bad queries in query analyser to identify possible improvements; it also lets you compare two queries after you have made changes.
The other thing you need to look at is indexes. You should feed one of your traces back into the Index Tuning Wizard in SQL Server Profiler - this will give you advice on what indexes will benefit your typical workload (and will in fact do all the work for you creating the indexes.)
Ivan
July 7, 2006 at 5:33 am
Johnny,
I'd go along with most of Ivan's suggestions, but I would offer a note of caution about using the index tuning wizard. It can be a very useful starting point in suggesting indexes, but that's all I'd let it do for me. I certainly wouldn't let it have free rein to go and create (and DROP!) indexes as it sees fit, which it will happily do if it is given the chance. Also, don't run it against a production server, even if it's just to get recommendations. It creates numerous temporary indexes and statistics to perform the various analyses, which is something I certainly wouldn't want to happen to any of my production databases.
Phil
July 7, 2006 at 6:05 am
Indeed, backup your production database, restore it on a non-production machine, and run your tests on that, if at all possible.
You still need to get the traces from the production server but in my experience it doesn't impact too badly, maybe around 4%. You'll impact slightly less on the production server if you run profiler on a seperate computer and grab the trace over a network.
As Philip says, take the index tuning wizard suggestions as a guide and be careful about what you DROP. Also bear in mind that it will only offer suggestions based on the trace you feed it, so ideally the trace you feed it should be something as representative as possible (e.g. if you only feed it queries over X ms, it will only optimise for those queries, and may suggest that you drop indexes helping all the queries that are currently running OK.) Having said that it will happily script the index creation for you and you can then do another trace to compare the effect of any changes, repeat etc. as an iterative process.
Certainly going from minimal or no indexes to well designed indexes can have a _massive_ effect on your performance, I've seen up to 1000% personally.
July 7, 2006 at 8:55 am
When you say you want to "check the box" I think you have to look beyond index tuning. There are some free utilities that can analyze server and database settings, security, and other issues. They won't find every possible problem, and you don't want to blindly implement every recommendation they make. They are not a substitute for being proficient with Profiler and Index Tuning Wizard, but they might make it look like you know what you are doing. Using any of these assumes you will have permission to install software at the site you're visiting.
You can get the Best Practices Analyzer for SQL 2000 from Microsoft at http://www.microsoft.com/downloads/details.aspx?FamilyID=b352eb1f-d3ca-44ee-893e-9e07339c1f22&DisplayLang=en
You can get a free trial version of Quest's Spotlight on SQL Server from http://www.quest.com/spotlight_on_sql_server_enterprise/software_downloads.aspx
This is a great program that has a Configuration Suggestions function that will point out some of the same issues the Best Practices Analyzer will complain about, but it also will look for databases that haven't been backed up recently, SQL Agent jobs that have failed, database files that are running out of room to grow, and many other issues. It will also let you look at running queries to see blocking, I/O performance, long-running queries, and so on. It doesn't log events like Profiler, it displays in real time almost everything you could get from SQL Profiler and Performance Monitor. You don't need to be an expert to use it, just fire it up and if something shows up blinking red you click on it to drill down to the cause. I would recommend trying it out on a test system before your trip so you have some familiarity with it. Note that you will need a SQL sysadmin login on the server to use it, and if you want to monitor the Windows environment (more disk I/O and memory statistics) you will require a Windows sysadmin login.
There are other SQL monitoring & tuning utilites out there that you can get free trial versions of, some may even be better, but I recommend Spotlight because that is what I am familiar with.
July 7, 2006 at 9:17 am
Might want to setup SQL Health and History monitor or run a baseline on System Monitor/Perf Monitor as well.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 7, 2006 at 9:18 am
Also, given the broad task of 'checking the box', I don't think you want to only capture SQL Profiler data. I would also capture some Windows Performance Monitor metrics. SQL Profiler will tell you how SQL is behaving on the server, but not how the server is reacting to the load that SQL Server is putting on it. I would recommend setting up the Windows Performance Monitor to monitor some of the high level counters for Disk, Memory, CPU, and SQL Server. Just like SQL Profiler, the WPM high level look will allow you to determine if there is a specific area that you need to drill down and get a detailed look on. Here are a few high level counters that I usually include:
1. Physical Disk>>Average Disk Queue Length - this will tell you how your disk subsystem is doing with keeping up with read/write operations. You will see this spike up and down, but any time the average is remaining at a level > 2 for a period of time, I would be concerned.
2. Memory>>Available MB - this is a high level counter that shows overall server memory availability.
3. SQL Server: Memory Manager>>Total Server Memory, SQL Server: Memory Manager>>Target Server Memory - monitoring these 2 counters together will tell you how SQL Server is making use of the available server memory and if you would benefit from adding more memory.
4. Processor>>% Processor Time - this counter will tell you how busy your CPU is. You will see spikes up to 100% here and that is OK. I would begin to be concerned if your CPU is constantly running above 70%.
Monitoring the server itself along with SQL Server Profiler will give you a more comprehensive look at how your application is running on that specific box.
July 7, 2006 at 10:07 am
All are excellent suggestions however when evaluating a server that I've not seen before I find its best to start with the basics. Find and document the following information on the server:
1. Hardware Specs - CPU / RAM / Disk Drives / Drive subsystem configuration (RAID)
2. File Locations - location of various files such as TempDB, Data files, Transaction Log files, pagefile, etc.
3. Drive Space - How much disk space is currently used / available?
This will provide an overview of the system, a baseline if you will.
From there I would run a performance monitoring tool such as Microsoft's Performance Monitor Wizard. It's very easy to use and gathers a lot of data for analysis:
Then you could proceed to a more detailed / thorough analysis if there is evidence of problems or conflicts.
Good Luck.....
:smooooth:
July 7, 2006 at 10:13 am
July 18, 2006 at 4:14 pm
Sorry... I never get this notice on my email....
Tks to everyone for reply... I will go over...
Johnny
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply