Performace issue in SQL server 2008

Question

Performace issue in SQL server 2008

krishusavalia

SSCrazy

Points: 2385
More actions
June 7, 2011 at 7:45 am

#251312

Hi Everyone,
I am new into performance issue.I can post the Query or execution plan for my query which i need to improve performance.
I am using SQL server 2008. I have stored procedure, in that First I am running the query and load the data into temp table using the select into command(approximate 5 mins and about 200k records), And after that I am joining that temp table with other 50 table to get the data and load into the other table with select into command.
The whole stored proc used about approx. 30 mins.I check the execution plan and for most of the table it's doing index scan.
Can any one guide me how to improve performance of that query, what I have to look into the execution plan, because we are running that query every one hour.
Thanks in Advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post execution plans, query, table definition, index definitions. See http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 2

As Gila Said provide complete information

Execute these queries when user complain regarding performance or if you feel and then provide result of these queries

For Memory Presure

==================

select * from sys.dm_os_performance_counters

where counter_name like 'page life%'

For Queries Utilizations

========================

SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

WHERE STATUS !='SLEEPING'

ORDER BY CPU DESC

My Blog
www.aureus-salah.com

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

krishusavalia (6/7/2011)
Hi Everyone,
I am new into performance issue.I can post the Query or execution plan for my query which i need to improve performance.
I am using SQL server 2008. I have stored procedure, in that First I am running the query and load the data into temp table using the select into command(approximate 5 mins and about 200k records), And after that I am joining that temp table with other 50 table to get the data and load into the other table with select into command.
The whole stored proc used about approx. 30 mins.I check the execution plan and for most of the table it's doing index scan.
Can any one guide me how to improve performance of that query, what I have to look into the execution plan, because we are running that query every one hour.
Thanks in Advance

Let me get this straight - you are joining FIFTY TABLES to get data for a SELECT INTO?? You have absolutely no hope of that query performing efficiently if that is the case. You also have no hope of getting that query disassembled and improved on a forum either - it is way too complex of an evolution. You can hire a professional to help you and/or buy better hardware. That's about it. :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

tfifield SSCrazy Eights Points: 9655 More actions · Answer 4

krishusavalia (6/7/2011)
Hi Everyone,
I am new into performance issue.I can post the Query or execution plan for my query which i need to improve performance.
I am using SQL server 2008. I have stored procedure, in that First I am running the query and load the data into temp table using the select into command(approximate 5 mins and about 200k records), And after that I am joining that temp table with other 50 table to get the data and load into the other table with select into command.
The whole stored proc used about approx. 30 mins.I check the execution plan and for most of the table it's doing index scan.
Can any one guide me how to improve performance of that query, what I have to look into the execution plan, because we are running that query every one hour.
Thanks in Advance

First of all, 5 min to load only 200K records into a temp table is very bad performance in most cases. I've never personally seen any SELECT INTO of 200 K records take 5 min. Your problems with this procedure probably run deep and ALL of the logic in the procedure needs to be looked at by a professional to tune each part.

Todd Fifield