SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 SQL Server Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 10/04/2006 :  17:19:54  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
I am having performance issues with one of my mission ciricial SQL Servers. A Payroll, and Timekeeper Web Applications are using this SQL server as a back end. Users have been complaining about two weeks the SQL Server has been very slow. Each app has it's own web server, and both are performing slowly.

Basic specs, and performance data:

Dual 1Ghz Xeons
2GB Memory
High End SCSI Drives, and Controllers RAID 5

One Database is about 6GB
Another about 11

Buffer Cache Hit Ratio is always around 95%
60 to 80 trx per second avg
Average Processor Usage 26% unless data syncs are running
and then processor will Avg. about 48%

The apps that are hitting the server are web apps using sp_executesql to run selects, updates, inserts, and deletes.
(seem to impact the server processor and memory very little)

Any suggestions as to what I may check to further diagnose the problem?

Thanks,

TSQLMan

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/04/2006 :  18:01:07  Show Profile  Reply with Quote
1. Capture some of that SQL in SQL Profiler and run the Index Tuning Wizard on it.
2. Increase memory to 3GB.
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 10/04/2006 :  18:09:36  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
I will try that, although I will be a little hesitant to let it make any changes. The databases were not developed in house, and up until the last two weeks, been very efficient. Altough the memory upgrade should have been done long ago.

Thanks,
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/04/2006 :  18:13:52  Show Profile  Reply with Quote
quote:
I will be a little hesitant to let it make any changes.

You don't have to make the changes, but at least you'll know if indexing is a problem - it may not be. If it is, then you can either add them yourself or go back to the developers and suggest the indexes.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/05/2006 :  02:06:01  Show Profile  Reply with Quote
6GB and 11GB is pretty big for a time keeping database!

Do you have maintenance procedures for REINDEX / INDEXDEFRAG and updating the statistics? Also recompile (although sounds like you don't have any Stored Procedures) - if you didn't recently reboot that server might be worth stop/start the SQL Service to kill the stale cache, and let it make a fresh one. (Date/time is at the top of the SQL Error Log - E.M. Server : Management : SQL Server Logs)

Did anything change 2 weeks ago - at the time people started noticing it was slower?

Is SQL on its own box? or is it sharing the box with IIS? (The first would be a better answer!!)

Has anything been added/changed on the box recently?

Any DNS changes which could have fouled up the routing (or searching!) of network packets?

Is the SQL port open to the web (and possibly getting loads of twits trying to guess the SA password? If you are not sure turn on RECORD UNSUCCESSFUL LOGIN which will then show them in the SQL log - Enterprise Manager : Right click the Server : Properties : [Security] : Audit level : "Failure")

Kristen
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 10/05/2006 :  07:58:51  Show Profile  Reply with Quote
Our friends over at sql-server-performance has some pretty good articles when it comes to performance tuning.

http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp

I'm no expert on performance tuning but indexing is in every case critical to an OLTP database. I'd probably look for locking/deadlocking issues and run Profiler to find any long-running queries and work on those first. Then maybe create some sort of table partitioning. I've had great performance gains in partitioning critical tables into "current" and "historic" tables...might be worth considering in addition to/instead of hardware upgrades.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 10/05/2006 :  09:42:58  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

6GB and 11GB is pretty big for a time keeping database!



Not if this is from a certain company based in Chelmsford, Massachusetts. They do not exactly give you a way of archiving/deleting any of the old records. We have punch records from the last 6 years.

If this is the same company that I am thinking of, their helpdesk always whines about defragging indexes. I think their DBAs just tossed them that line to get people to shut up once in a while. Still, it may get you past their level one support.

Are both databases are timekeeping applications?
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 10/05/2006 :  10:51:02  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
quote:
Originally posted by Kristen

6GB and 11GB is pretty big for a time keeping database!

Do you have maintenance procedures for REINDEX / INDEXDEFRAG and updating the statistics? Also recompile (although sounds like you don't have any Stored Procedures) - if you didn't recently reboot that server might be worth stop/start the SQL Service to kill the stale cache, and let it make a fresh one. (Date/time is at the top of the SQL Error Log - E.M. Server : Management : SQL Server Logs)

Did anything change 2 weeks ago - at the time people started noticing it was slower?

Is SQL on its own box? or is it sharing the box with IIS? (The first would be a better answer!!)

Has anything been added/changed on the box recently?

Any DNS changes which could have fouled up the routing (or searching!) of network packets?

Is the SQL port open to the web (and possibly getting loads of twits trying to guess the SA password? If you are not sure turn on RECORD UNSUCCESSFUL LOGIN which will then show them in the SQL log - Enterprise Manager : Right click the Server : Properties : [Security] : Audit level : "Failure")

Thanks,

Kristen



SQL is on it's own Box.

We have Maintenance Plans, and weekly a manual dbcc dbreindex is performed on all the tables, I recently did and sp_recompile on the database.

We do have sprocs, but reports are the only thing, that uses them.

As a matter of fact, we did have some routing changes, but I will have to research to see if they could have possibly affected that server.

We have intrusion detection running, and I have done a profile on login attempts already.

Thank you for the suggestions, I have something else to look at. I remember thinking it could be nework related, but that is not my area of expertiese.


Edited by - TSQLMan on 10/05/2006 10:57:31
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 10/05/2006 :  10:55:54  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
quote:
[i]Are both databases are timekeeping applications?



No one is Timekeeper, and other other is HR/Payroll
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/05/2006 :  11:20:55  Show Profile  Reply with Quote
Have you verified that it is really SQL Server that is slow? Maybe something is wrong on the web servers.

I would use profiler to see what the long running transactions are to verify that it is really SQL Server that is slow.



CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/05/2006 :  13:50:18  Show Profile  Reply with Quote
Bit late if you don't already have it! but it would be good to have some baseline tests - including some local to the SQL box itself - to enable easy testing of what part of the system is suffering slowdowns.

Kristen
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 10/07/2006 :  21:22:24  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
quote:
Originally posted by Kristen

Bit late if you don't already have it! but it would be good to have some baseline tests - including some local to the SQL box itself - to enable easy testing of what part of the system is suffering slowdowns.

Kristen



Turned out to be an authentication issues on the SQL Server, after I deleted and recreated spn's on the SQL Server Service User it straightened right up.

TSQLMan

Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 10/10/2006 :  01:29:40  Show Profile  Reply with Quote
Hi
I could not get this
"Turned out to be an authentication issues on the SQL Server, after I deleted and recreated spn's on the SQL Server Service User it straightened right up."
Can you please elaborate?
Regards
Nitin
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 10/10/2006 :  17:42:58  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
spn stands for Service Principal Name. Basically anythime a user is running a web application using Windows integrated Authentication, when the client requests data from the web server, the web server then passes on the users credentials to the SQL Server, which creates a double hop scenario, forcing Kerberos Authentication vs. NTLM. A Kerberos double hop scenario requires and spn.

In our case web app users were continually requesting a piece of data on another SQL Server, which forced the SQL Server Service user to authenticate to another SQL Server, and that was failing, which was intermittently causing a delay in the return of data, therefore giving the appearance, that there was a performance issue with the SQL Server.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000