March 7, 2011 at 11:41 am
Hello all! I just started as a database coordinator at a fairly well funded non profit coming from a high end wealth management boutique where my role was database developer against SQL 2000. In my previous position I worked with the sys admins to make sure everything was running smoothly. we had the resources and downtime meant folks loos there jobs.
Here, I walked into a 'red alert'. I know, I know... what did I expect? The main OLTP is a membership/POS application called SpectrumNG running on SQL 2005 sp2 and it appears to be running at 5% capacity. The machine dedicated to this application...a DELL poweredge 2950
2 quad zeon processors
8 GB memory
5 physical disks divided into 3 logical partitions (raid unknow - nobody knows for sure)
* OS
* Logs
* Data
There are two instances on the machine and I'm not sure what the second one does. I'm actually not entirely sure about this and not sure how to check how many instances there are. Will there be two sets of services?
What I experience is over all sluggishness to the point where navigating menus and directories is painfully slow. The biggest and most obvious indicator of a problem is task manager showing little CPU usage and a massive page file of 7.8 GB.
Again, I come from an environment where things are normally running smoothly so this kind of situation is very new to me. I need to make sure I'm asking the right questions.
Using a third party tool (DBArtisan) I find a heavy 'Paged Memory Used' statistic and a PAGE IO LATCH bottleneck but I'm not sure what to do with this information.
It's a production machine in use long hours so down time or rebooting or doing anything risky is dangerous.
My plan is to
1) Contact software vendor and obtain the recommended setting for SQL
2) Figure out the Raid set up (not sure how yet)
3) Determine our actual licensing for SQL 2005 (I noticed when I set affinity masks they reset themselves and I've read that can be due to licensing issues - perhaps we are not licensed to take advantage of all 8 cpus?
4) Open a case with Microsoft?
Any advice or thoughts from the forum are welcome. This is now my responsibility, I'm new at this organization and feel in over my head troubleshooting there main OLTP which is badly limping along.
Thanks every one!
John:w00t:
March 7, 2011 at 11:59 am
Take it a piece at a time...The first piece you need to look at is the backups of the database. Ensure you have some and that they are good (meaning you can restore from them). I would get a test server/environment setup if possible, even if it is just a instance on your desktop. This will help you get familar with SQL Server and the tools that come with it.
The RAID configuration: Since you are on a Dell server you can see if the Dell Management tools are installed. This will tell you exactly how the disk are setup (RAID configuration, disk errors, hardware errors, etc).
If you are running SQL Server 2005 look for SQL Server Configuration Manager under All Programs > Microsoft SQL Server 2005 > Configuration Tools. If that opens up you should have "SQL Server 2005 Services" and in the right pane will list all the services the server has. The database engine will show up as "SQL Server (Instance Name)". If you see "SQL Server (MSSQLSERVER)", this is the default instance.
What I experience is over all sluggishness to the point where navigating menus and directories is painfully slow.
Are you referring to getting around on the server or this is what the users experience in the application?
I would definiately contact the vendor and gather information and documentation from them. Though be careful in what they tell you about SQL Server. Most vendors do not have the technical side of SQL Server down so they can mislead you. If they start giving you specifics on what configuration changes you should make on SQL Server (especially if they give you a script), verify the commands in Books Online or Google them to see what they actually do.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 7, 2011 at 12:01 pm
Once you get a chance this is a good video/script to check out from Brent Ozar that can help you get an idea of what you are looking at regarding the SQL Server setup:
BLITZ! 60 minute SQL Server Takeovers[/url]
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 7, 2011 at 12:11 pm
Thanks everyone I really appreciate the community! When I say overall sluggishness I mean navigating around on the machine AND user experience. I sat down with a user the other day and their simple reports takes minutes and minutes to run. It's really bad. I can't help feeling that the huge page file activity in task manager is a clue. Should I expect to see that so high? 8 GB?!
March 7, 2011 at 12:22 pm
I just read that SQL 2005 Standard only supports 4 CPUs!
http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx
We have two quadcore Xeon e5410 @ 2.33GHz. What happens when you try and run standard 2005 with more than 4 processors?
March 7, 2011 at 12:27 pm
You can probably setup a perfmon log and begin capturing information on how SQL Server and the OS is performing. SQLServerPedia.com has some good videos on using perfmon and focus on SQL Server side of things.
You need to get an understanding first of how things are configured and setup. If you start focusing on one aspect of things it may throw off something else. Getting with the vendor and get their documentation and such would be your best starting point I think.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 7, 2011 at 1:58 pm
You have 8 gig RAM, but how much is allocated to SQL ?
March 7, 2011 at 2:19 pm
jligda 35032 (3/7/2011)
I just read that SQL 2005 Standard only supports 4 CPUs!http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx
We have two quadcore Xeon e5410 @ 2.33GHz. What happens when you try and run standard 2005 with more than 4 processors?
This is based on sockets - not on the number of cores. Since you only have 2 quad core processors there are no issues.
Is this x64 or x86? If x64, you need to make sure you set the max memory for each instance running on the server so they don't take all of the memory. There are also known issues with x64 and Standard Edition where SQL Server's memory will get paged out if the max memory is set too high.
To get around that, you need to set the locked pages in memory option for the service account running SQL Server. However, in Standard Edition you need to be on SP3 CU4 (I believe) - and also set a startup flag or trace flag to enable that option.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 7, 2011 at 2:23 pm
In Management studio memory is minimum 4096MB and MAX 6400MB
March 7, 2011 at 2:31 pm
We are x86 so no issues there. Thanks for the info on the sockets vs. cores. That's good to know. The next thing I think I need to do, is figure out what we are licensed for. I'm beginning to think SQL Standard was installed accidentally instead of Enterprise. The director was surprised to hear we are running standard sp2.... uh boy.
March 7, 2011 at 2:34 pm
You just have to attack it a step at a time. Generally, gathering metrics on the system, general performance metrics, and wait states is the best beginning (after setting up backups as was already proposed). Once you have some understanding of how the system is behaving, what is causing things to run slow, then you can start looking at DMVs to find the sources of the problems (probably, but not necessarily, bad code, sounds like you have a 3rd party app, which means tuning code might not be possible). Check that you have statistics maintenance in place, that you're defragging the indexes (although it sounds like a small system, so this might not be much of an issue). Just traditional, step at a time, gather resources and follow the numbers, types of performance tuning.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply