Solved

SQL 2005 performance changes

Posted on 2010-09-12
18
642 Views
Last Modified: 2012-06-29
Hi All,
I have taken over a company with a server dedicated to running a SQL 2005 based program, written in Java to access a database using a client server type connection.
What we are experiencing is a slow down of the product when we get a large influx of users connecting at once. (around 15 at a time)
I have spoken with the company's DBA and we have discussed many different options but wanted to put it out there for possible solutions to my plight.
The system is Windows server 2003 SP2
details of the processors and memory are in screen shot below Machine detailsThe system only has 4GB of RAM and we are talking about upgrading to X64 OS and allocating more memory to the SQL instance if possible?
However I am yet to find out if I need to upgrade my SQL to X64 version also to make benefit of more RAM if purchased?
While talking with the DBA he suggested my disks may be too slow also. We have run the attached benchmarks and it came back below average it would seem. See screen shot below for results: Benchmark resultsI have also attached screen shots of two sets of disk details (Physical and virtual) from the DELL open manage software: plus a windows disk management screen shot showing extra space I didn't know I had!! Windows Disk Part Info Virtual disk details Physical disks detailsSorry this seems like so much to absorb, but I need to make sure I cover all angles of what I can do to improve the performance of this database.
Can anyone suggest what the best steps I should take? I can upgrade the RAM to 20Gb if needed and the OS to X64 (SQL to X64 maybe) and If the disks are in a poor Raid config or a slower setup, I will happily change disks, RAID and/or buy faster disks etc.
The server is a DELL 2950 FYI.
NB: This application database is well indexed and runs well on other servers with larger disk arrays and more memory etc at other organisations.
Thanks for the help and I look forward to some suggestions
Craig
MrBungle50
0
Comment
Question by:mrbungle50
  • 9
  • 5
  • 4
18 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 167 total points
ID: 33659180
Increasing RAM alone without increasing the processor wont help much..
Kindly let me know the below details:

1. CPU Usage
2. SQL Server version and Service pack details ( Recommended to stay up to date with latest Service packs)
3. Is there any other major application running in the server like Exchange or Sharepoint or Antivirus, etc.
4. Do you have Maintenance plans to rebuild heavilgy fragmented indexes frequently. ( If not then try rebuilding all indexes now and try again)
5. Do you have sufficient disk space ( NTFS disks should have minimum of 20% free space)
0
 

Author Comment

by:mrbungle50
ID: 33659199
Hi there thanks for the prompt reply.
*How would you like m?e to get you the CPU usage?
*SQL details
   Microsoft SQL Server Management Studio Express      9.00.2047.00
   Microsoft Data Access Components (MDAC) 2000.086.3959.00    (srv03_sp2_rtm.070216-1710)
   Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
   Microsoft Internet Explorer  7.0.5730.11
   Microsoft .NET Framework  2.0.50727.1433
   Operating System  5.2.3790
*No other applicatiosn running at all......... anti virus is client only
No rebuilding of indexes that I am aware of, but will check this isn't automated by the software (will get back to you)
I will increase disk space over the next few days, for some reason *as shown in the screen shot is the spare disk space not used at all.
Thanks, will be in touch shortly with the missing details.
Craig
MrBungle50
0
 
LVL 8

Assisted Solution

by:ludo_friend
ludo_friend earned 333 total points
ID: 33659210

--
The system only has 4GB of RAM and we are talking about upgrading to X64 OS and allocating more memory to the SQL instance if possible?
However I am yet to find out if I need to upgrade my SQL to X64 version also to make benefit of more RAM if purchased?
While talking with the DBA he suggested my disks may be too slow also. We have run the attached benchmarks and it came back below average it would seem. See screen shot below for results:
--
yes, you need to upgrade to x64 o/s and x64 sql to take full advantage of >4gb ram.
how big is your database?
please download and install this - http://www.microsoft.com/downloads/en/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en . while your server is running slowly, this will give you a good indication of why. This will also identify any missing indexes and time spent on various waits.

to determine if ram is your problem, please run up performance monitor and add the following SQL counters.
•Buffer cache hit ratio
•Page life expectancy
•Page reads/sec

if your buffer cache hit ratio is below 99.9 at any time, ram is (at least one of) your problem.

--
index fragmentation

please run the below on the db in question to identify fragmented indexes (can take a long time on large databases)
--
SELECT ps.database_id, ps.OBJECT_ID,

ps.index_id, b.name,

ps.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

AND ps.index_id = b.index_id

WHERE ps.database_id = DB_ID()

ORDER BY ps.OBJECT_ID

GO
--

please let me know the results so I can advise a solution.
0
 

Author Comment

by:mrbungle50
ID: 33659269
Thank You LudoFriend
I have added the perfmon features you asked but the performance dash board gives me the following error when running the setup.sql file
Msg 195, Level 15, State 10, Procedure usp_GetPageDetails, Line 27
'object_schema_name' is not a recognized built-in function name.
Msg 156, Level 15, State 1, Procedure usp_GetPageDetails, Line 42
Incorrect syntax near the keyword 'as'.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'usp_GetPageDetails', because it does not exist or you do not have permission.

I am logged on as Administrator with domain admin permissions? maybe I don't have the correct access for the master DB?
I will run the other query when I have the dashboard running first.
Thanks
Craig
MrBungle50
 
0
 
LVL 8

Expert Comment

by:ludo_friend
ID: 33659290
Hi there, there is an SQL script (Setup.sql) that needs to be executed to create the functions that make the reports work.
it's in the installation folder.

if the server has been on for a while (you'll get a datetime compare error when running a report), you may have to follow the steps here  - http://blogs.msdn.com/b/chrissk/archive/2008/06/19/performance-dashboard-error-difference-of-two-datetime-values-caused-an-overflow-at-runtime.aspx



0
 

Author Comment

by:mrbungle50
ID: 33659417
That's right I ran the setup.sql script and got the above error message.

Msg 195, Level 15, State 10, Procedure usp_GetPageDetails, Line 27
'object_schema_name' is not a recognized built-in function name.
Msg 156, Level 15, State 1, Procedure usp_GetPageDetails, Line 42
Incorrect syntax near the keyword 'as'.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'usp_GetPageDetails', because it does not exist or you do not have permission

What can this be?
0
 
LVL 8

Expert Comment

by:ludo_friend
ID: 33659421
looks like you might be out of luck on this approach unless you upgrade to SP2/SP3
[snip]
OBJECT_SCHEMA_NAME is a new function added in SP2.

http://blogs.msdn.com/sqltips/archive/2007/03/23/object-name-enhancement-and-object-schema-name-addition-in-sql-server-2005-sp2.aspx
[/snip]
0
 

Author Comment

by:mrbungle50
ID: 33659454
OK I'll look into that upgrade, Obviously the later the version the better in my opinion. Will check this out and get back to you.
Cheers
Craig
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33661118
>> *How would you like m?e to get you the CPU usage?

Check out usage of CPU in task manager for few minutes and let me know the average value ( rough figure).

>> Microsoft SQL Server Management Studio Express      9.00.2047.00

You have SQL Server 2005 Express edition which has the following limitataions:

* 1 CPU
* 1 GB Ram

And hence I think SQL is suffering for resources.
Even this might be the reason for your server issue and hence its suggested to use Standard edition of SQL Server in order to use your Server CPU (2 CPU) and 4 GB RAM Efficiently.

>> Thanks, will be in touch shortly with the missing details.

sure, kindly let me know those details so that something can be identified out of it.

>> 'object_schema_name' is not a recognized built-in function name.

As mentioned by ludo_friend above, apply SP3 for SQL Server which has several bugs fixed in it..
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:mrbungle50
ID: 33667690
Wow  I had no idea the limitations of SQl express.  The previous IT provider was rubbish..zero fore-thought.
OK I need to look into getting things moving with regard to the changes needed.
I'll have some performace stats for you when the staff get back and all start logging on and the system starts to drag.
Will check wiht my open licence and see if we have the full SQL installation for starters.
Thank you so much..
Will post details as they come to hand.
Craig
MrBungle50
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33672943
>> Wow  I had no idea the limitations of SQl express.

Hope you have Express edition in your machine.
To confirm it, just run this query

SELECT SERVERPROPERTY('edition')
0
 

Author Comment

by:mrbungle50
ID: 33677597
Thanks for that query, It turns out I have the standard edition. But my management studio is express so the memory issue is not relevant here, Thankfully
Will still look at other ways to increase the performance, and post successes / failures here
Cheers
Craig
MrBungle50
0
 
LVL 8

Assisted Solution

by:ludo_friend
ludo_friend earned 333 total points
ID: 33677847
Have you managed to get the service pack applied?

Also did you manage to have a look at the counters I mentioned? - can you also show me the disk queue of the data and log drives?

Also did you check out the index fragmentation?

I'd also have a look at missing indexes - http://www.sql-server-performance.com/articles/per/Identify_Missing_Indexes_Using_SQL_Server_DMVs_p1.aspx
0
 
LVL 8

Expert Comment

by:ludo_friend
ID: 33677854
also how large is the company database(s)
0
 

Author Comment

by:mrbungle50
ID: 33678069
Hi Ludo,
we haven't had a great deal of usage over the past 2 days due to lots of staff being off!
I have enabled the counters and will monitor over the next few days.
We have more staff back this week so should be a better chance of observation.
I will find out how large the db is and post it back for you.
Cheers
Craig
MrBungle50
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33679206
ok, Just configure Perfmon to run during peak load in the server and steps on how to schedule, run and analyze Perfmon counters here:

http://www.mssqltips.com/tip.asp?tip=1039
http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
0
 

Author Comment

by:mrbungle50
ID: 33836695
Thanks for the advice everyone, but the issue looks like it was disk speed.
I have replaced the disks to 15k disks and incraesed space and it seems to have done the trick.
Thanks everyone, I will share the points across all who helped.
0
 

Author Closing Comment

by:mrbungle50
ID: 33836701
Every answer was a great help and I learnt lots about SQl that I didn't know.
Thanks a lot
Craig
Mrbungle50
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

There are two basic ways to configure a static route for Cisco IOS devices. I've written this article to highlight a case study comparing the configuration of a static route using the next-hop IP and the configuration of a static route using an outg…
this article is a guided solution for most of the common server issues in server hardware tasks we are facing in our routine job works. the topics in the following article covered are, 1) dell hardware raidlevel (Perc) 2) adding HDD 3) how t…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now