February 12, 2009 at 7:56 am
First I'm a Remedy developer, not a DBA, but I realize there are tons of variables that can affect performance, so I'm looking for typical or frequently missed configuration tweaks. We are running SQL Server 2005 on a 3.5 GHz, dual core, dual processor system with 4 GB of RAM. Our database is used only for reporting, and there is very little else running on the box. We are dealing with 3 million records. Should it take 17-20 seconds to return a simple count? Another, relatively simple query takes 2-4 hours to run. I realize that can be very much affected by the query and indexes. But, 4 hours?!
We have not changed any settings from the default, out of the box settings.
Any suggestion/help will be greatly appreciated.
TIA!
Brian
February 12, 2009 at 9:26 am
The first thing you need to do is look at the query plans for these queries. Until you see those you can't really get a feel for any changes that need to be made.
You can also use the DMV's to work on performance issues as well. A starting point might be this:
SELECT
SUBSTRING(DEST.text, (DEQS.statement_start_offset / 2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(DEST.text)
ELSE DEQS.statement_end_offset
END - DEQS.statement_start_offset) / 2) + 1) as statement_text,
DEQS.execution_count,
DEQS.total_elapsed_time,
DEQS.total_logical_reads,
DEQS.total_logical_writes,
DEQS.total_physical_reads,
DEQS.total_worker_time,
DEQP.query_plan
FROM
sys.dm_exec_query_stats AS DEQS CROSS APPLY
sys.dm_exec_sql_text(DEQS.sql_handle) AS DEST CROSS APPLY
sys.dm_exec_query_plan(DEQS.plan_handle) AS DEQP
ORDER BY
DEQS.execution_count desc
There are also DMV's for index usage and missing indexes as well.
Are you running 64-bit or 32-bit? If you are running 32-bit you may want to look up AWE.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 12, 2009 at 11:36 am
Thanks, Mr. Corbett!
February 12, 2009 at 11:41 am
If performance has been degrading over time, in addition to doing what Jack suggests, you should update the statistics, probably with a full scan, and set up a schedule to do this on a regular basis. You'll also want to look around for a good script that will defragment your indexes. The best ones will check the fragmentation level and then either defrag or reindex (rebuild) based on the fragmentation level.
"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
February 12, 2009 at 12:40 pm
You can also check how much RAM the SQL Server is using.
-Roy
February 15, 2009 at 11:11 pm
as per all suggest you can check execution plan and also use Database Engine tuning advisor ... help you lot to create indexes .....
Raj Acharya
February 17, 2009 at 7:57 am
Hi Mr.Corbett,
Could you tell me how can I work with plan in xml ? Thx a lot.
March 3, 2009 at 10:24 pm
briangillock (2/12/2009)
Should it take 17-20 seconds to return a simple count? Another, relatively simple query takes 2-4 hours to run. I realize that can be very much affected by the query and indexes. But, 4 hours?!
No... it shouldn't take that long even against millions of rows. And, it doesn't matter what you do to the server or indexes if it's, ummmm... crap code because the indexes might not even get used because of the way the code was written.
If it's not proprietary, you might want to post that "simple query [that] takes 2-4 hours to run" so we can tell you some of the things that might be crushing performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2009 at 8:25 pm
Take a look and see if there is any database maintenance/optimization. If not, then that is a great first step. As Jeff said, there is no reason things should run that slow. Remedy, like most vendor packages, provide woefully inadfequate performance optimization maintenance plans - usually canned out of the box.
Here is my standard maintenance/tune-up list:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
Hint: Write scalable code to start with !
You also might want to pay a visit to:
http://www.sql-server-performance.com
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 6, 2009 at 5:35 am
rudy komacsar (3/5/2009)
Here is my standard maintenance/tune-up list:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
Hint: Write scalable code to start with !
You also might want to pay a visit to:
http://www.sql-server-performance.com[/quote%5D
I'm not trying to pick a fight here, at all, just seeking a bit of clarity, but if you have an unknown slowdown you run a recompile on all the procedures? In production? And you automatically do a reindex too, without checking the fragmentation levels?
To a large degree, we should be working like doctors, first do no harm. I'd think those are somewhat shotgun blast approaches to something that would be better served with more targeted approaches.
"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
March 6, 2009 at 5:57 am
Round I
DBCC UPDATEUSAGE
But should this not be run after upgrading to SQL 2005?
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
why do you have to run this after rebuilding indexes, as rebuilding indexes will update statistics too.
March 6, 2009 at 6:15 am
I thank you all for your help. For someone who has no training or experience at this, it's daunting. Anyway, all the code is out of the box from one application or another. I have to assume that other people don't have this issue. I've been around Remedy a lot and not had this issue myself. Which makes me think it's some errant setting. Again I thank you all for the help.
March 6, 2009 at 9:09 am
Hey Grant - no problem - was just trying to get some suggestions out there based on the limited information - personally I index-defrag before index rebuild and then only based on percentages. 10% for defrag, 30% for rebuild - the least intrusive the better - most of my databases now are Tb sized and 24x7 !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 6, 2009 at 9:30 am
rudy komacsar (3/6/2009)
Hey Grant - no problem - was just trying to get some suggestions out there based on the limited information - personally I index-defrag before index rebuild and then only based on percentages. 10% for defrag, 30% for rebuild - the least intrusive the better - most of my databases now are Tb sized and 24x7 !
Too Cool. That's all good then. I was just concerned that someone reading that would think they'd found the grail of performance tuning. Sorry for the friction.
"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
March 6, 2009 at 10:31 am
Hi there,
which way is better to manage indexes (rebuilt,reorganization)?
Is better create maintenance plan by Wizard and schedule it for example twice too week..
or is better find fragmentation indexes "by hand" ? ("with sys view")
I think second is better, because when I create maintenance plan it (reorganization or rebuilt all indexes)
Which way use Master of DBA? 🙂
Thanks for answers Radek
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply