I need help with my query. This table has 144 million rows and this is a stage table ( where we insert data from another table). I didn't have indexes before and the job had been running for more than 9 hours. I added the non clustered index to this table with multiple columns ( AdvertiserName,MediaPlanName,MediaPlanNumber, CreativeDescription) because this combination makes it unique. But even now execution plan shows Table Scan and not non-clustered index scan and no improvement with performance.This is the query below, and it is taking very long time to summarize data into csv file using SSIS. How can I improve performance on this query? Please help!! It is taking very long time to run. :(SELECT AdvertiserName, AdvertiserID, MediaPlanNumber, MediaPlanName, PublishingSiteName, SiteName, Week_Begin_Monday, CreativeDescription, SUM(CAST(ViewCount AS BIGINT)) ViewCount, SUM(CAST(ClickCount AS BIGINT)) ClickCount, Media, Segment_Name, Segment_CD, Group_Name, Group_CD, Channel, LOB, Creative_Message, Creative_Category, Creative_Type, SUM(GRP) GRP, Intended_Delivery_ScreenFROM Stage_MM240(NOLOCK)GROUP BY AdvertiserName, AdvertiserID, MediaPlanNumber, MediaPlanName, PublishingSiteName, SiteName, Week_Begin_Monday, CreativeDescription, Media, Segment_Name, Segment_CD, Group_Name, Group_CD, Channel, LOB, Creative_Message, Creative_Category, Creative_Type, Intended_Delivery_Screen
Performance Help with query -144 million row table
Posted - 2015-04-30 : 12:12:31
Anything else running on that server?Anything else querying that table ? (Scary that you have NOLOCK ... if its not being accessed by anyone else then get rid of it? It is NOT a performance enhancement bandaid!!)If nothing else querying that table - i.e. its basically a Data Warehouse - should Snapshots be turned off? or the database set to READ_ONLY? (Would the DB being Read Only actually provide any useful performance "Hints" to SQL? Its not something I've tried)If there is anything else running on that server (I mean something like a non-SQL Service) then perhaps SQL is configured to use all Memory and it is thrashing with whatever else is running and needs memory? (Might be that SQL is configured to use ALL Memory, and not even reserve any for O/S. I suppose if that is the case then dropping the MAX Memory could help.What about TEMPDB? Is that on a separate drive to the Database? Configured to be a reasonable size, rather than auto-growing? I presume this query will use acres of TEMPDB, but its only a guess - maybe not? Can you pre-create TEMPDB to have minimum number of VLFs - or is that just "regular" DBs?I seem to remember Tara mentioning having multiple files for TEMPDB ... and then I have a vague recollection of that approach going out of fashion! - what's the current Best Practice on that?How about?? running this on, say, 10% of the data to analyse how much resources / TEMPDB / etc that uses, and then maybe use that information to best-guess how to configure for the full-fat run?