February 22, 2009 at 3:58 am
everything is fine with our server including: CPU, memory etc. there's no bottleneck etc. but when we try to run a functionality in our application which executes one stored procedure, the server becomes so slow. while the same procedure with the same application and the same amount of data works well in another server.
I know its so general question but please let me know what would you do in such situation
February 22, 2009 at 5:21 am
here's my suggestions:
1. update statistics:
2: can you show us the procedure? it might be the parameter sniffing issue(search here on SSC)
3: can you show us the execution plan? that shows EXACTLY what SQL was doing, and tells us where the slow spots where so we can offer specific advice on how to fix it.
Lowell
February 22, 2009 at 5:30 am
Thanks Lowell 🙂
Actually, I'd asked a developer to change her stored procedure to not to create a dynamic view many times in a cursor but immediately after she modified the stored procedure we had the same problem. But after a while when we tested it again, the problem was gone. I think the problem was due to the code of stored procedure but I wonder why immediately after the modification, we had the same problem.
February 22, 2009 at 5:34 am
oh there can be a whole world of difference between servers hosting the same database.
- do you rebuild indexes on a regular basis ?
- are the statistics up to date ?
- is the data model exact ? (indexes,..)
- is the data volume and cardinality comparable ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 22, 2009 at 5:47 am
peace2007 (2/22/2009)
... create a dynamic view ...
this is a huge NONO !
- doing so you'll create pressure on your db catalog, especially when the proc is executed in parallel !
- this will also cause recompiles
try to avoid dynamic sql as much as possible in stored procedures !
"The curse and blessings of dynamic SQL"
www.sommarskog.se/dynamic_sql.html
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2009 at 1:40 am
I'm back with the same problem
Let me explain the whole problem better.
The sqlserver is slow generally; everything with its database is slow and of course when that stored procedure is running it gets time out error
- do you rebuild indexes on a regular basis ?
so far as I know rebuilding indexes automatically may cause performance problem is that true or I can create a job to rebuild indexes on a regular basis
- are the statistics up to date ?
I'm a new DBA how should I know?
- is the data volume and cardinality comparable ?
Could you explain more about relation between data volume and cardinality?
March 10, 2009 at 2:10 am
peace2007 (3/10/2009)
...so far as I know rebuilding indexes automatically may cause performance problem is that true or I can create a job to rebuild indexes on a regular basis
Most of us schedule a sqlagent job to rebuild indexes or use a scheduled maintenance plan.
If you are on a DEV or Enterprise edition, many indexes can be rebuild "online".
Anyway it is a pain you have to take !
(If your bricks aren't stacked correctly, don't expect a stable house)
- are the statistics up to date ? I'm a new DBA how should I know?
I run sp_updatestats on each database every couple of months. (is still an old habit)
- is the data volume and cardinality comparable ? Could you explain more about relation between data volume and cardinality?
Is the data composed in the same ratio ? e.g. 30% of white eggs, 70% of brown eggs.
Are these ratios equal for both sqlserver instances / databases ? This may infouence the usability of indexes for the queries you designed... hence deliver different execution plans.
You could post an execution plan activating it in Management Studio. Query pane, option "Query/Include actual execution plan" (ctrl+M) and excute your stored procedure.
Then you can click on the execution plan, rightclick on the execution plan and select "Save as"
If you compress it (ZIP) you can attach it in a reply to the forum.
It doesn't reveal data, but gives the full explanation of what your proc is doing, including some statistics (e.g. expected number of rows,..).
Datavolume ... a 10 MB db will most likely be serve faster than that same database containing 10GB of data.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2009 at 2:24 am
peace2007 (3/10/2009)
The sqlserver is slow generally; everything with its database is slow
Run profiler (or a server-side trace) for half an hour to an hour. Find the worst performing queries/procs in that period. Take them and see what you can do to tune them. That may be code rewrites, it may be indexing, it'll probably be both. Implement the changes and then repeat the profile, find and fix process.
If you need help with the optimising, post the query and the execution plan and someone will very likely help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2009 at 3:37 am
if you are using a profiler in production server its better to use the dmv or dmf.
March 10, 2009 at 4:58 am
DMVs don't store everything and they are not a replacement for SQL Trace.
On a busy server use a server-side trace rather than the profiler GUI. The trace has minimal impact.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2009 at 1:29 am
I've rebuilt indexes and run sp_updatestats then I executed following to find 10 most consumers of CPU:
SELECT TOP 5 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, st.[text], qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY (total_logical_reads + total_logical_writes) Desc
the statistics of found queries in previous step is attached. Could you tell me what the problem is with them?
Besides, some stored procedures get data through linked servers and since such stored procedures are called so many times, we get time out error or we experience low performance. Is there any other choice for linked server in such situation?
another question is that PF Usage is so high in task manager, what may cause this?
March 11, 2009 at 2:08 am
hmm ... a whole bunch of clustered index scans.
That indicates absence of usable indexes for the predicates you provided.
Even tough stats indicate only 1 row to return, it will perform a full scan of all datapages (clustered index) or index leaf level pages.
That's why one should avoid scans at all times.
HowTo ? Create an index on the column(s) you use in the queries (if feasable)
It's a petty the execution plans only state the sproc you execute, not the actual queries text.
btw implement some naming conventions for your stored procedures ! Don't start the names with "sp_" because sqlserver will start to look for the sproc in MASTER db !
A little extra overhead, but when executed many times, this is something one easily can avoid.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2009 at 4:20 am
I've run a server side trace. now, when I want to open it in DTS for analyzing it says the file is in use. I tried to stop and close the trace but it says the trace with this id is not found.
How should I resolve that?
I'm so tempted to query a DMV and create indexes accordingly 😉
March 11, 2009 at 6:21 am
peace2007 (3/11/2009)
I've run a server side trace. now, when I want to open it in DTS for analyzing it says the file is in use. I tried to stop and close the trace but it says the trace with this id is not found.
Query sys.traces and see what traces are active.
I'm so tempted to query a DMV and create indexes accordingly
Go ahead. The indexes may not be 100% optimal, but they might help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2009 at 8:08 am
Just prepare your action a little bit, so you don't end up having a dozen of similar indexes.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply