Performance issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Performance issue

Author

Topic

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-10 : 10:16:03

Hi all,

I'm new to sql server, I'm not sure how I can handle this problem. I'm researching on methods to trace the problem that could be causing performance slow down. The program runs from the client side application, Selects n records from a table and updates n records in that table. I used SQL Server Profiler to collect the trace, I had all the columns checked under performance in the trace file. It is a huge file, I'm able to see Showplan Text,Showplan All, Showplan Text,Showplan Statistics Profile,Showplan All for Query Compile, Performance Statistics in the trace. I'm not sure how I can proceed further in tracing the problem here.. Please give me your thoughts or suggestions.

Thanks a lot for all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 10:18:28

whats the problem? is it taking too long? are you getting time out errors? please elaborate.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-10 : 10:19:23

Is you Server slow or Query?

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-10 : 10:31:07

Below are the sql queries ran from the program:

SELECT oitm,co from risg where cls <>'Y'  and co >= :v1 and co <= :v2 order by co ASC;
OUTPUT:n records
update risg set cls = :v1 , cls_dt = :v2 where oitm = :v3
OUTPUT: updates the n records

Table risg contains 430000 records, I ran the program to update 10999 records in the table, it took about 2 minutes. It is taking long time compared to before and in the oracle database for the same number of records it is taking much lesser time.

It could be the application code that is causing the slowness.But I'm not sure how to determine that.
Please help. Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 10:33:50

this is sql server forum not oracle. so please post this in some oracle forum like www.dbforums.com

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-10 : 10:37:51

Visakh, I just posted Oracle database to compare with the other database that the program runs faster..But we are having the slowness with the SQl server database..

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-10 : 10:39:44

quote:
Originally posted by psangeetha

Visakh, I just posted Oracle database to compare with the other database that the program runs faster..But we are having the slowness with the SQl server database..

Thanks

You still have not answered out questions regarding SQL Server.

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-10 : 10:51:08

Sodeep, the program when ran it is slow.. When I just run the query by itself on the Management studio it is fine..

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-10 : 17:09:27

We just faced another performance issue with 2 other programs using the same table risg. Could it be something to do with the indexes in the table?? Does it needs to be rebuild or the table needs to be organized??

Please give me your thoughts/suggestions.

Thanks a lot

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-10 : 20:02:16

Check the fragmentation level of indexes. If needed rebuild or reorganize.

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-11 : 13:12:28

Is there like a query to check if the index is fragmented?? Does it have to be checked or will it make sense to just rebuild it?

Thanks for your help

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-11 : 13:32:51

I used this query to find the fragmented indexes that are used by in this program. One is pk index and another one is fk index.
Both the indexes show less than 30% avg_fragmentation_in_percent.
Does it mean the rebuilding index will not work in this case?

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

Thanks again

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-11 : 13:38:56

Search for TARA's Script .It will check fragmentation level and determine whether rebuild or reorganize is needed.

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2009-02-12 : 15:24:51

I rebuilt 2 indexes those were 96% fragmented. We ran the program again and it didnt make any difference at all. Is there anything else we could do to improve the performance?

Thanks a lot

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 17:40:46

quote:
Originally posted by psangeetha

I rebuilt 2 indexes those were 96% fragmented. We ran the program again and it didnt make any difference at all. Is there anything else we could do to improve the performance?

Thanks a lot

What does Execution Plan say if query is performing slow?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources