Serious performance issues in SQL Server 2008

Get help on databases - MySQL, Oracle, Access, etc.
User avatar
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

Post by Tribble »

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
Image
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.
Image
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: Serious performance issues

Post by Ron2K »

Kia kaha, Kia māia, Kia manawanui.
User avatar
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

Post by hamin_aus »

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.
O RLY?
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 :P
Image
User avatar
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

Post by Tribble »

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
Image
User avatar
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

Post by hamin_aus »

Still waiting

Also just noticed your 10Mb network... what is going on Tribble, do you work in 1997?
Image
User avatar
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

Post by Tribble »

I saw that too - am trying to get hold of the IT guy
Image
User avatar
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

Post by Tribble »

They do have 16GB of ram on the machine.
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: Serious performance issues in SQL Server 2008

Post by rustypup »

could also be AV hitting the SQL log...

if everything was fine until 3 weeks ago, what changed?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
User avatar
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

Post by Tribble »

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 :shock:
Image
User avatar
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

Post by hamin_aus »

Tribble wrote:They changed the DNS server
Eh? Why?
Tribble wrote:added more "employees
wat
Tribble wrote:installed IIS.
duhfuq
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.
Tribble wrote:Every time I modify my front end they manually copy it to more than 120 machines :shock:
Well to be fair if that is a production system why are you constantly modifying it :?:
Please tell me your "frontend" isnt Access... I will leave this thread and never retur
Image
User avatar
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

Post by Tribble »

Then I will not tell you that. ;-) There are so many changes because the bank keeps changing their lending policies :lol: 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 ;-)
Image
User avatar
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

Post by hamin_aus »

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?
Image
User avatar
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

Post by Tribble »

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.
Image
User avatar
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

Post by Tribble »

Image
Here is the memory specs - available and total
Image
User avatar
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

Post by hamin_aus »

Did you just reboot that box? You have heaps of free memory!!!
Image
User avatar
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

Post by Tribble »

Nope not to my knowledge. All that runs on that box is SQL and Access.
Image
User avatar
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

Post by Tribble »

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?
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: Serious performance issues in SQL Server 2008

Post by rustypup »

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?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
User avatar
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

Post by Tribble »

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*
Image
GreyWolf
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

Post by GreyWolf »

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
User avatar
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

Post by hamin_aus »

All of them :lol:

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.
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: Serious performance issues in SQL Server 2008

Post by rustypup »

Tribble wrote:"unrecognisable database format"
:| 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)...

what version of access were these "apps" created in?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
User avatar
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

Post by Tribble »

@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.
Image
User avatar
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

Post by Tribble »

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 :lol:
Image
Anakha56
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

Post by Anakha56 »

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.
Post Reply