Serious performance issues in SQL Server 2008
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Serious performance issues in SQL Server 2008
Hi - no idea where to put this so thought I would start with it here.
We have a machine Running Windows Server 2008. On that we have have SQL Server 2008 R2. It is suddenly very slow. I ran Resource Monitor and this is what I got
As you can see - SQL is overusing the disk. It has something like 178 278 497 reads if you expand the disk section.
They have about 100 people connecting to SQL. What can I do to speed this up? We did not have this problem 3 weeks ago.
We have a machine Running Windows Server 2008. On that we have have SQL Server 2008 R2. It is suddenly very slow. I ran Resource Monitor and this is what I got
As you can see - SQL is overusing the disk. It has something like 178 278 497 reads if you expand the disk section.
They have about 100 people connecting to SQL. What can I do to speed this up? We did not have this problem 3 weeks ago.
- Ron2K
- Forum Technical Administrator
- Posts: 9050
- Joined: 04 Jul 2006, 16:45
- Location: Upper Hutt, New Zealand
- Contact:
Re: Serious performance issues
Optimize your database.
Part 1: Use indexing
Part 2: Write better queries
Part 3: Apply advanced indexing and denormalization
Part 4: Diagnose database performance problems
Part 5: Optimise database files and apply partitioning
(Mod note: moving thread to the Database section, because that's what the problem is.)
Part 1: Use indexing
Part 2: Write better queries
Part 3: Apply advanced indexing and denormalization
Part 4: Diagnose database performance problems
Part 5: Optimise database files and apply partitioning
(Mod note: moving thread to the Database section, because that's what the problem is.)
Kia kaha, Kia māia, Kia manawanui.
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: Serious performance issues in SQL Server 2008
O RLY?Tribble wrote:As you can see - SQL is overusing the disk. It has something like 178 278 497 reads if you expand the disk section.
You don't troubleshoot SQL performance issues with resource monitor...
Those disk reads are for the entire time the MSSQL service has been running, so if the box has been up for 3 months, that's how many reads have happened in total over that time.
It's not uncommon for SQL to cause out of bounds error exceptions on these tools when the number of reads gets beyond a bigint on 32-bit systems. Which leads me to my first suggestion - reboot the box. A fresh start might be all it needs.
If that fails, as Ron pointed out via links, I'd look at what (if any) maintenance plans you are running on your server...
You should be re-indexing and updating statistics nightly for a busy DB
Also, what other software is running on this box - since you dont have a dedicated DBA I assume this isnt a dedicated SQL server, but a dumping ground for random apps...
How much RAM does this box have? Maybe SQL is starving for resources - RAM is usually the culprit here, try and give SQL more RAM. A busy instance of SQL these days will want nothing less than 8GB RAM minimum. You can set SQL's memory threshold in SQL server.
Second culprit is disks. Do you use a decent SAN that's properly managed and monitored, or are the disks local?
If local, have you got your SQL MDF files on a RAID5 array, and the LDF files on a RAID 1 array, both of them non OS disks and preferably with nothing else that could steal IOPS?
If you are confident delving into SQL, you could run a profiler trace to capture a workload, and play it back in SQL's database tuning advisor. This toll will analyse your SQL and suggest indexes and statistics which could improve your databases performance
On a lighter note, maybe just run profiler with it's predefined long running query trace, find the query's that take longest to execute (anything over 500ms) and look at ways to make them better. SQL can help with this if you paste the T-SQL code in query window in management studio and run it using 'Include the Actual Execution Plan' button which is close to the Execute button. SQL will then suggest indexes that will make these bits of SQL run faster. Take those with a grain of salt tho.
Oh, and hire a DBA if the performance of your SQL server is something you care about
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
Thanks both of you - I will play tonight and see if there is any difference in performance.
@Jamin - I never knew that about the reads. Thanks. Will post server specs now now
@Jamin - I never knew that about the reads. Thanks. Will post server specs now now
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: Serious performance issues in SQL Server 2008
Still waiting
Also just noticed your 10Mb network... what is going on Tribble, do you work in 1997?
Also just noticed your 10Mb network... what is going on Tribble, do you work in 1997?
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
I saw that too - am trying to get hold of the IT guy
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
They do have 16GB of ram on the machine.
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: Serious performance issues in SQL Server 2008
could also be AV hitting the SQL log...
if everything was fine until 3 weeks ago, what changed?
if everything was fine until 3 weeks ago, what changed?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
That is what I asked them. They changed the DNS server, added more "employees", installed IIS. I see they do have a backup program which I think tracks the changes but I would "assume" that they wouldn't do that constantly - during the day. Will ask him.
Psst - do you know that he doesn't know how to deploy software from the server?! Every time I modify my front end they manually copy it to more than 120 machines
Psst - do you know that he doesn't know how to deploy software from the server?! Every time I modify my front end they manually copy it to more than 120 machines
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: Serious performance issues in SQL Server 2008
Eh? Why?Tribble wrote:They changed the DNS server
watTribble wrote:added more "employees
duhfuqTribble wrote:installed IIS.
I dont see how ANY of this is going to help the performance.
Schedule a reboot this evening. Make that step 1 and take it from there.
This is almost comical.
Well to be fair if that is a production system why are you constantly modifying itTribble wrote:Every time I modify my front end they manually copy it to more than 120 machines
Please tell me your "frontend" isnt Access... I will leave this thread and never retur
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
Then I will not tell you that. There are so many changes because the bank keeps changing their lending policies and wanting more reports. And as we progress we realise we constantly underestimate the level of idiots. So we have to build more and more controls into the program. We have to split the tasks into more specific tasks so that more people are needed to see the one sale through to completion.
No idea why they changed the DNS - they wanted it on a faster machine (goodness knows why - the other machine wasn't slow)
By more employees I mean they got more call agents. More people connecting to SQL
IIS was needed - trust me
No idea why they changed the DNS - they wanted it on a faster machine (goodness knows why - the other machine wasn't slow)
By more employees I mean they got more call agents. More people connecting to SQL
IIS was needed - trust me
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: Serious performance issues in SQL Server 2008
It sounds to me like you have made your code more and more complicated over time and also added more and more users and more and more reports.
Your hardware finally reached the point where it could not cope with the demands your system now places on it.
You should have seen this coming. You can't honestly tell me you expected this to just keep on working no matter how silly you got with it?
I'll bet that for every screen of data in your system SQL has to full table-scan thru 10 tables that probably aren't properly indexed and have statistics that are way out of date.
You've also probably over-normalised the database in an attempt to segregate data so that only certain people can see certain bits of info.
I don't know how you write queries but hopefully you use stored-procedures and views and don't rely on Access to generate crappy awkward SQL on the fly
How much of your database traffic is reporting? Do they only run reports at specific times or constantly during the day?
I'll trust you on IIS, but really not, since it should have gone on a separate server as now IIS is going to compete with SQL for resources - especially important if you are using IIS to present reports from SQL or run some sort of web front-end...
Is any of this sounding like it hits the mark, or am I way off?
Your hardware finally reached the point where it could not cope with the demands your system now places on it.
You should have seen this coming. You can't honestly tell me you expected this to just keep on working no matter how silly you got with it?
I'll bet that for every screen of data in your system SQL has to full table-scan thru 10 tables that probably aren't properly indexed and have statistics that are way out of date.
You've also probably over-normalised the database in an attempt to segregate data so that only certain people can see certain bits of info.
I don't know how you write queries but hopefully you use stored-procedures and views and don't rely on Access to generate crappy awkward SQL on the fly
How much of your database traffic is reporting? Do they only run reports at specific times or constantly during the day?
I'll trust you on IIS, but really not, since it should have gone on a separate server as now IIS is going to compete with SQL for resources - especially important if you are using IIS to present reports from SQL or run some sort of web front-end...
Is any of this sounding like it hits the mark, or am I way off?
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
I will have to reread that a few times.
Bear ( )in mind that I am an Access developer with very very basic SQL knowledge that I taught myself (yes that is the dodgy part).
I do not think that I have over-normalised my data. I have different front ends for the different processes: Sales, QA, Verification, Checking, TeamLeaders, Management. They merely pull as much detail as is required from the tables. I have two main tables that contain the data. I have many feeder tables for the combo boxes.
As with most of my programs - they are meant to be interim solutions. This has been running for a year and a half now and I have just removed last years data. About 199k records. The developer I had working on the web frontend has quit and I am now trying to find out what he has done and see if anyone can complete it. Probably not so the whole year's work will be scrapped and we will get another developer..... another story.
Stored what? No I am teasing you. But sadly most of my most complex queries are in Access and not SQL as I have no confidence in my ability to actually get the query to do what it should. I have a little more confidence in my VBA knowledge - but not much. I understand things only when I can compartmentalise them - and code just doesn't seem to have compartments.
Most reporting is active - buy that I mean they click a button to see how sales are going, how many calls were made - the usual. Other reports are run at the end of a day / week. Day ends at 7pm.
I am under correction - I mentioned that IIS had been installed - I did not mean on this server. It is on the other SQL box that is running like a bomb. No speed issues there. It is virtually the same programme - same quantity of data - same queries badly structured running through Access - just a different product. Fewer users though.
If it wasn't for my other box running perfectly well - I would agree that I am a terrible designer and really should rewrite all my tables and queries But nope - it is not only me to blame for this.
Bear ( )in mind that I am an Access developer with very very basic SQL knowledge that I taught myself (yes that is the dodgy part).
I do not think that I have over-normalised my data. I have different front ends for the different processes: Sales, QA, Verification, Checking, TeamLeaders, Management. They merely pull as much detail as is required from the tables. I have two main tables that contain the data. I have many feeder tables for the combo boxes.
As with most of my programs - they are meant to be interim solutions. This has been running for a year and a half now and I have just removed last years data. About 199k records. The developer I had working on the web frontend has quit and I am now trying to find out what he has done and see if anyone can complete it. Probably not so the whole year's work will be scrapped and we will get another developer..... another story.
Stored what? No I am teasing you. But sadly most of my most complex queries are in Access and not SQL as I have no confidence in my ability to actually get the query to do what it should. I have a little more confidence in my VBA knowledge - but not much. I understand things only when I can compartmentalise them - and code just doesn't seem to have compartments.
Most reporting is active - buy that I mean they click a button to see how sales are going, how many calls were made - the usual. Other reports are run at the end of a day / week. Day ends at 7pm.
I am under correction - I mentioned that IIS had been installed - I did not mean on this server. It is on the other SQL box that is running like a bomb. No speed issues there. It is virtually the same programme - same quantity of data - same queries badly structured running through Access - just a different product. Fewer users though.
If it wasn't for my other box running perfectly well - I would agree that I am a terrible designer and really should rewrite all my tables and queries But nope - it is not only me to blame for this.
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
Here is the memory specs - available and total
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: Serious performance issues in SQL Server 2008
Did you just reboot that box? You have heaps of free memory!!!
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
Nope not to my knowledge. All that runs on that box is SQL and Access.
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
Ok something weird is happening now. Programs that were working this morning are now corrupt and unrecognisable. The broken ones were accessed at 09:50 - all of them. We were not working on them at the time. These are the masters stored on the servers. Luckily we have copies but I want to know what could corrupt a whole lot of programs at the same time. What could have done this?
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: Serious performance issues in SQL Server 2008
this backup solution... is it rated for SQL 2008?
and is this server running on tin or hyper-v?
also, what's your social security number and favourite password?
and is this server running on tin or hyper-v?
also, what's your social security number and favourite password?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
No idea what backup solution they are using - will check when the network guy finally gets back to me.
This is running on a physical machine if that makes sense. No tin ? or hyper-v.
Social security? I am not an American my dear friend. And the password is incorrect
/Edit
I give up. Spent an hour making changes one of the programs. Saved it and then saved a copy. Both are corrupt and say "unrecognisable database format" - yet there were no errors while I was working on it. The IT guy wanted to copy the changed program to another machine so he closed it - and it will not open again. *Sigh*
This is running on a physical machine if that makes sense. No tin ? or hyper-v.
Social security? I am not an American my dear friend. And the password is incorrect
/Edit
I give up. Spent an hour making changes one of the programs. Saved it and then saved a copy. Both are corrupt and say "unrecognisable database format" - yet there were no errors while I was working on it. The IT guy wanted to copy the changed program to another machine so he closed it - and it will not open again. *Sigh*
-
- Registered User
- Posts: 4754
- Joined: 06 Aug 2003, 02:00
- Processor: PHENOM II 945
- Motherboard: Asus M4A78
- Graphics card: HIS ICEQ 4850 1GB
- Memory: 4GB CORSAIR XMS II 1066
- Location: , location, location!
Re: Serious performance issues in SQL Server 2008
May I just ask what bank this is?
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
- H. L. Mancken
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: Serious performance issues in SQL Server 2008
All of them
Tribble I have no idea whats going on there....
I will say that your application architecture is amazing.
You have SQL Server - one of the most robust, intelligent, easy to performance-tune and troubleshoot database management systems around...
And you have Access - an impossibly stupid, sloppy and uncontrollable abortion
You chose to use SQL Server as a data dumping ground and you chose to do all your "smarts" in Access
This was the only possible end result, I'm afraid.
Tribble I have no idea whats going on there....
I will say that your application architecture is amazing.
You have SQL Server - one of the most robust, intelligent, easy to performance-tune and troubleshoot database management systems around...
And you have Access - an impossibly stupid, sloppy and uncontrollable abortion
You chose to use SQL Server as a data dumping ground and you chose to do all your "smarts" in Access
This was the only possible end result, I'm afraid.
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: Serious performance issues in SQL Server 2008
did someone roll out any updates to .NET? because this looks like a driver fault... (or someone has made some fundamental change to the server itself)...Tribble wrote:"unrecognisable database format"
what version of access were these "apps" created in?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
@Puppy 2007
@Jamin - that is why I am learning c# and visual studio. If there was time I would learn to write better queries in SQL and use SQL views - but I have had no time. Seems lots of people want Access applications at the moment.
@Wolfie - Standard. But I am doing the work for a Voice switching company. They just sell the range of cards.
@Jamin - that is why I am learning c# and visual studio. If there was time I would learn to write better queries in SQL and use SQL views - but I have had no time. Seems lots of people want Access applications at the moment.
@Wolfie - Standard. But I am doing the work for a Voice switching company. They just sell the range of cards.
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Serious performance issues in SQL Server 2008
An update.
Moved the database to a different server and there are no latency issues and it is wonderfully quick. Methinks the other server needs a looking at.
And on another positive note - I am attending 10776 so I will finally know if what I am doing is correct. Problem is that it puts me out of contact for 5 days. Bet my clients are not going to be happy
Moved the database to a different server and there are no latency issues and it is wonderfully quick. Methinks the other server needs a looking at.
And on another positive note - I am attending 10776 so I will finally know if what I am doing is correct. Problem is that it puts me out of contact for 5 days. Bet my clients are not going to be happy
-
- Forum Administrator
- Posts: 22136
- Joined: 14 Jun 2004, 02:00
- Processor: Ryzen 1700K
- Motherboard: Asus X370
- Graphics card: Asus 1060 Strix
- Memory: 16GB RAM
- Location: Where Google says
Re: Serious performance issues in SQL Server 2008
I'm leaning on memory corruption... I reckon RAM has gone faulty...
JUSTICE, n A commodity which is a more or less adulterated condition the State sells to the citizen as a reward for his allegiance, taxes and personal service.