[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server Performance

Posted on 2012-08-13
12
Medium Priority
?
155 Views
Last Modified: 2014-12-10
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)?
0
Comment
Question by:riverbank
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 38287477
It sounds like you have most of the basic issues covered. Ie Fragmented Indexes etc. You it sounds like its more about database design in this instance.

Are there any specific queries that seem to be the issue?

Have a look at the following article on how to find long running queries.

http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/
0
 
LVL 1

Assisted Solution

by:Damag3d
Damag3d earned 1000 total points
ID: 38287506
Are there any specific stored proc's that run when you experience the slow downs?

Also, just to be clear, are the T-logs for both databases on the same array?
0
 
LVL 1

Author Comment

by:riverbank
ID: 38287984
I will look at getting that article run through tomorrow to identify the longer queries.

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).
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
LVL 30

Expert Comment

by:Rich Weissler
ID: 38288056
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...  :-( )
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 38288087
I agree with Razmus approach. Maybe some wait stats too if your feeling confident.
0
 
LVL 1

Assisted Solution

by:Damag3d
Damag3d earned 1000 total points
ID: 38288246
Could you run this query when the application slows down to get an idea of what is sitting in memory at the time? This should rule out any problems with parallelism too.

SELECT
    r.scheduler_id,
    r.session_id,
    r.command Command,
    t.text SQL_Statement,
    r.blocking_session_id Blocking_Session_ID,
    r.total_elapsed_time/1000 Total_Elapsed_Time_Seconds,
    r.cpu_time CPU_Time,
    s.login_name Login_Name,
    s.[host_name] [Host_Name],
    s.[program_name] [Program_name],
    s.memory_usage Memory_Usage,
    r.status [Status],
    db_name(r.database_id) Database_Name,
    r.wait_type Wait_Type,
    r.wait_time Wait_time,
    r.wait_resource,
    r.reads Reads,
    r.writes Writes,
    r.logical_reads Logical_Reads
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE
    r.session_id <> @@spid  --and blocking_session_id = -4
ORDER BY 2 DESC
0
 
LVL 1

Author Comment

by:riverbank
ID: 38292127
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.
0
 
LVL 30

Assisted Solution

by:Rich Weissler
Rich Weissler earned 500 total points
ID: 38292183
> Is it possible the DB on the new server is somewhere trying to pull information from the old server box?

It's not impossible.  But without other information, I would be inclined to guess that the problem is a web application which is pulling more far more information necessary.  ("More than necessary" based on the fact that your 'other' instance doesn't have the same issue.)  I'd continue with the blog EvilPostIt suggested, and see what the large queries look like.  If you have the web developer available, it might be useful to put your heads together with the results...
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 38292245
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.
0
 
LVL 1

Author Comment

by:riverbank
ID: 38349746
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
0
 
LVL 1

Author Comment

by:riverbank
ID: 40490966
Dear all,

The issue was found to be fragmentation in the database thank you for your assistance.
0
 
LVL 1

Author Comment

by:riverbank
ID: 40490969
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A procedure for exporting installed hotfix details of remote computers using powershell
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question