August 24, 2007 at 8:45 pm
Hi,
My database is using nearly 150 users today morning some of the users are complaing my database performing very slow result & respose.
How can increase the my database Performance what are ther steps i can follow to increase the my database performance please guide me & plz give some more good suggestions plz
URGEN
August 24, 2007 at 10:29 pm
your situation as serious as it is.. your explanation leaves TONS of opens holes.. please give more details as to the problem...
slow logins...slow query runs...please be more specific as to the problem...Good Luck
Thanks
DHeath
August 25, 2007 at 1:06 am
Thanku for ur reply
Hi,
Actually my database all quries and logins entire database is performing very slow what i can do
help me
Regurds
subu
August 27, 2007 at 5:27 am
without specifics, we can't diagnose the problem.
since this is a general question for now, here's a general answer and things to look for:
is your application doing SELECT * FROM SOMETABLE, without a where statement, and then filtering the results? if it is doing it one time to load a datatable for client side use, that's not bad, but if it is doing it more than once, its inefficient. consider changing the application. grabbing more data than necessary should be avoided. if possible, every select should have a WHERE statement to limit the results .
Are you loading and drop down lists with more than 100 items? in vb6 and on web pages, this can cause a big performance hit. consider displaying only the selected value, and adding a button to open a form/page with a grid, and change the selection from there. for example, I found a DDL where it was loading ~550 names, and it added4-8 seconds to the form load, depending on the end users' CPU speed.
Identify a specific situation(s) that runs slow. (when I click this report...) turn on Profiler and catch the actual statement that is being run. paste it into Query Analyzer and view the execution plan of it. If you see table scans in the query, you'd want to consider adding/modifying indexes to the table(s) to speed up the query. you might also want to rewrite the query. Post the queries here that are slow for suggestions; there's lots of knowledgeable people who read this every day.
make sure your query has SARGable arguments in it. if you are searching for a varchar, like last name or something it requires a scan. if there is no index, it's a table scan, which is the slowest, and if there IS and index, then youd have an index scan, which is better. ideally, you want to search a datatype that is indexed in order, like a datatime field, an integer, etc. a query with only INDEX SEEK is going to give you the fastest times. search SSC for "sargable" for some good details.
Are you doing any row by row processing? if any While Loops or cursors are being used, post the code here for suggestions on how to change them to a set based statement.
If it is job-threatening serious, consider calling in a consultant to review your situation and help rewrite the queries. free help here can only go so far;
Lowell
August 27, 2007 at 7:06 am
I always find it funny when someone says "The system seems slower today. Are you running anything?"
Nice and vague. Of course I'm not "running anything" but what the heck are they doing to "the system"? I always get a chuckle when you find out that the "system" they're accessing is the local area network, and they're trying to retrieve a file.
August 27, 2007 at 8:15 am
Lowell has some good advice, but also how do you know things are slow? It sounds silly, but slow is subjective. Ask someone to run a query, have a stopwatch, and then have them tell you when it's slow. 10 seconds is a long time to watch the screen. you also have to benchmark things on a "good" day and learn what to expect from the system. You can use paper and pen here to just mark times for queries that people often run.
Make sure nothing is happening on the network/workstations. Patches, updates, etc. might slow down all systems. Check queries on the server and then dig into what's wrong.
If you don't know how to look at a SQL Server and see what's slow, then it's going to be hard for us to help you. It really takes some effort to learn about SQL Server and then try to diagnose things. You'll need more details other than things are slow.
Some hints:
Look for blocking (sp_who2)
Check the overall server, CPU, RAM use, disk use, etc.
Check specific functions the users do and find out how slow they are. Benchmark against direct queries on the server.
August 28, 2007 at 10:55 am
Steve is right. Pls proceed with the steve's suggestion. Do reply if still you are not able to identify the issue.
1. Check for SQL Server Logs if any notifiable errors appeared.
2. Check for the database status or any table is fragmented.
3. Check is there any long running /Resource consuming queries found in the Server.
Thanks.
Eshwar.
August 28, 2007 at 11:29 am
First do some gerenral maintenance to be sure things are par, depedning on sql version
DBCC INDEXDEFRAG or DBCC DBREINDEX -- to cleanup the indexes
then
sp_updatestats -- to update the databases statistics for the stored data
then
DBCC UPDATEUSAGE -- to cleanup the allocation information to reflect accurately under the hood
As well, check to make sure the databases AUTO CLOSE option is not turned on.
See how that goes and then proceeed from there.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply