SQL Server Performance
We have SQL Server 2008 R2 running on Windows Server 2008 R2 and have a custom built SQL database running that is accessed from an ASP web interface.
There are two identical sites configured (on different ports) that access two different SQL databases (that were identical when setup but now contain different sets of data, but both databases have a similar volume of data - around 1500 client records). These two sites are configured to store data for two different geographical client data sets (One for North, one for South).
We have performance issues when browsing through just the North sets of data. Performing the same browsing and searching on the South, it works quickly whilst on the North it takes 10-30 seconds to navigate the data.
We have checked the server performance (CPU, RAM, HDD activity, Networking, etc) and nothing is overworked when the issues occur in the North data (or ever, this was a new server to try and improve the performance as these issues existed on a previous Windows Server 2003 machine with SQL Server 2000).
We have also tried stopping all of the services for the South database and site and the issues still persist with the other data set. The server is not performing any other roles at all.
We have also performed all SQL Server maintenance tasks to compact and tidy up both databases and the transaction logs and fully re-indexed both. The server has been rebooted following all of these tasks but the performnce issues in just one database persists.
The data is located on two separate RAID arrays, one for the transaction logs and one for the databases.
Does anyone have any insight or suggestions as to what we can do to improve the performance of accessing data in the one site (that is virtually identical to the second site in all but the data records - which are all simple text fields with minimal data - less than three sentances)?
There are two identical sites configured (on different ports) that access two different SQL databases (that were identical when setup but now contain different sets of data, but both databases have a similar volume of data - around 1500 client records). These two sites are configured to store data for two different geographical client data sets (One for North, one for South).
We have performance issues when browsing through just the North sets of data. Performing the same browsing and searching on the South, it works quickly whilst on the North it takes 10-30 seconds to navigate the data.
We have checked the server performance (CPU, RAM, HDD activity, Networking, etc) and nothing is overworked when the issues occur in the North data (or ever, this was a new server to try and improve the performance as these issues existed on a previous Windows Server 2003 machine with SQL Server 2000).
We have also tried stopping all of the services for the South database and site and the issues still persist with the other data set. The server is not performing any other roles at all.
We have also performed all SQL Server maintenance tasks to compact and tidy up both databases and the transaction logs and fully re-indexed both. The server has been rebooted following all of these tasks but the performnce issues in just one database persists.
The data is located on two separate RAID arrays, one for the transaction logs and one for the databases.
Does anyone have any insight or suggestions as to what we can do to improve the performance of accessing data in the one site (that is virtually identical to the second site in all but the data records - which are all simple text fields with minimal data - less than three sentances)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another possible resource -- Chapter 5 of Troubleshooting SQL Server, does a good job of stepping one through identifying missing Indexes in about 30 pages. Sounds like all the indexes in the database are up to date, etc... but I assume it isn't impossible that one index might have gone missing for some reason.
EvilPostITs link gives instructions which will should give you the SQL text of the queries which may be "gumming up" the system. That's definitely the best first plan of attack. If that doesn't give anything definitive, you might need to run a Profiler Trace, and see if there is a huge number of small queries causing problems. (Seen that when the developer was Cursor happy... :-( )
EvilPostITs link gives instructions which will should give you the SQL text of the queries which may be "gumming up" the system. That's definitely the best first plan of attack. If that doesn't give anything definitive, you might need to run a Profiler Trace, and see if there is a huge number of small queries causing problems. (Seen that when the developer was Cursor happy... :-( )
I agree with Razmus approach. Maybe some wait stats too if your feeling confident.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear all,
Thank you for your suggestions thus far.
I was about to run the queries suggested when I noticed on task manager quite excessive Network traffic going in and outside the box.
Traffic is averaging 55mbps - 110mpbs with the top 2 offenders being:
w3wp.exe and sqlservr.exe
what is concering is that the size of the databases are relatively small.
(28MB and 200MB respectively)
I checked the DSN's and they are set to localhost to check for some obvious bad routing.
I am wondering if this excessive network traffic is responsible for the high network usage.
Is it possible the DB on the new server is somewhere trying to pull information from the old server box?
Regards,
Riverbank.
Thank you for your suggestions thus far.
I was about to run the queries suggested when I noticed on task manager quite excessive Network traffic going in and outside the box.
Traffic is averaging 55mbps - 110mpbs with the top 2 offenders being:
w3wp.exe and sqlservr.exe
what is concering is that the size of the databases are relatively small.
(28MB and 200MB respectively)
I checked the DSN's and they are set to localhost to check for some obvious bad routing.
I am wondering if this excessive network traffic is responsible for the high network usage.
Is it possible the DB on the new server is somewhere trying to pull information from the old server box?
Regards,
Riverbank.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have a look to see if these queries are doing a SELECT * when all they really require is a few columns. This would minimise the data being pulled across the network.
ASKER
Hello all we are currently awaiting for a reply from the customer regarding this issue but it looks likely that the queries being sent to the DB are the cause of the issue.
I will update once we know more.
Scott
I will update once we know more.
Scott
ASKER
Dear all,
The issue was found to be fragmentation in the database thank you for your assistance.
The issue was found to be fragmentation in the database thank you for your assistance.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for riverbank's comment #a40490966
for the following reason:
Database fragmentation was the cause.
Accepted answer: 0 points for riverbank's comment #a40490966
for the following reason:
Database fragmentation was the cause.
ASKER
In answer to the other points; There doesn't seem to be any standard places the slowdown occurs or running specific procs, it is fairly random but only ever occurs in the one database.
And the transaction logs for both databases are on the same array (as the databases are on the same array, but a different one that the T-Logs).