November 2, 2007 at 4:01 am
On occaision, when running the application or in the SQL Analyzer istself, there is a prolonged delayed in the execution of the queries.
When all is running well, the queries run in 536ms, 500ms 2500ms and even faster in the SQL Analyzer.
Running from Coldfusion during an intermittemt patch
16219ms, 38500ms, 30281ms
IN SQL Analyzer during an intermittemt patch
0s, 57s, 121s
Has anyone come across this problem before and how did you rectify it.
November 2, 2007 at 5:05 am
This can happen if there are other connections or processes accessing the database\database server at the same time that you are.
Typically queries will execute quicker against a database directly as opposed to running from an application or reporting tool
One way to check exactly what is happening is to turn on SQL Profiler before you execute your query and it will show you exactly what is a happening during the execution of your query.
For further analysis Save your Profiile and run the indexing wizard on it
This will point you to potential arears which can speed up your queries by adding indexes to your database
November 2, 2007 at 5:15 am
On the second server, where the database is the same in structure and programming, there is no problem at all.
The SQL when run directly against the DB, on the problematic server, has been taking over a minute to count through 700,000 recs
I ran the DB tuner yesterday and it pointed out a lot of indices which should be created, but this again would create different variances of the database in the different environments (dev/test/release/live)
November 2, 2007 at 5:27 am
The live database is the one you need to tune against as you want to index on this. You should always match your other environments to this.
You should run your tune during in a peak time on the live db to idenfiy any bottlenecks.
You would expect different hits against your dev and preprod environments.
November 2, 2007 at 5:57 am
The Live database runs fine, it is the development environment which I need to optimize but keep in line with the other servers too
November 2, 2007 at 6:36 am
That is to be expected in the Live environment vs Dev because
Generally a Live Server would be more powerful
a Dev Server would have a lot more database (for testing etc)
a Dev Server would possibly have more connections and load
developers are using mulitple connections eg dev app, database queries (query analyser)
You may have developers running back up and restores on database they are using for testing[/i][/b]
If the Live server is running fine i would leave well alone as too may indices are as bad as too few.
The preproduction environment is the place where you would perform any speed test as this should be an exact replication of your live enviroment.
When we are testing new releases\database updates the first thing we do is take a copy of the Live database (+app if required) back into the Preproduction and perform an upgrade test and performance optimisation.
November 2, 2007 at 7:03 am
kev_byrne (11/2/2007)
On the second server, where the database is the same in structure and programming, there is no problem at all.The SQL when run directly against the DB, on the problematic server, has been taking over a minute to count through 700,000 recs
I ran the DB tuner yesterday and it pointed out a lot of indices which should be created, but this again would create different variances of the database in the different environments (dev/test/release/live)
If you're comparing serverA to serverB, not only do you need to verify that the they have the same memory, the same services, the same disk subsystems, the same load, but then you have to be sure the data, as well as the structure, is identical and that the fragmentation of the indexes is identical and that the parameters used are identical. After all that you can start to compare serverA to serverB.
Have you run Profiler on the server? You can see what other queries are occuring at the same time as yours. This could be a quick and easy way to identify the issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 2, 2007 at 9:25 am
You should also look at the various properties of the DB's as well. My Dev edition platform seems to "like" setting the Auto-Close feature to ON on any databases I attach, and there's always that nasty growth factor with the DB's. If you script getting the databases over, you would likely end up with nasty autogrow factors, never mind ugly "starting database sizes". Same with TempDB (mine defaulted to a 1MB initial size, autogrowth @ 1MB increments: run a "big job" against that and just watch your disk fragmentation go nuts and performance is right out the window....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2007 at 2:42 am
I was analyzing the DB and found that it is quite fragmented.
Is there a quick and simple method to getting the DB back into good working order, without causing too much downtime and impacting the other users.
This tends to take up a lot of the system resources which are available at the time, while leaving all other users only getting a fraction of cpu time.
Can I get around this problem too.
(
I've been attempting to run a scheduled job with no success. This has limited features to it as I wanted to run 'DBCC indexdefrag' as part of the overall job.
ROUTINE:
Backup Database,
Check Database Integretiy
Rebuild Index
ReOrganize Index
Update Statistics
Shrink Database
)
November 12, 2007 at 5:18 am
There are a number of scripts available on this site that will analyze the indexes and perform a defrag or a reindex depending on the level of fragementation. It shouldn't cause any down time on your system. Although, the first time you run it, it's likely to slow things down a bit if your system is seriously fragemented. After that first run, schedule it to run periodically during a time when a bit of a slow down won't affect too many users. We run ours nightly. On some of our production systems with 40+ databases, the entire job runs in less than two minutes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply