Urgent! SQL Server 2000 problem! | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Urgent! SQL Server 2000 problem!

Hello, We are stuck with a third party tool that uses Large IN clauses in its SQL statements. The SQL statements go directly against a large DATA view that is a join of 24 tables. Each table has:
1000 Products
80 Markets
20 Retail Conditions
200 Periods (Timeframes) SQL Server 2000 (Standard)seems to crap out (is unresponsive) when we pass large queries with many IN clauses. We can’t change the SQL – only the database set-up.. Any idea? It’s supposed to be a known bug with Microsoft but their hot fix didn’t work…
1)Run Profiler when those SQL statements are running.
2)Run Index Tuning wizard to find out if there is Indexes to implementate to get performance. or if you allready know witch SQLs are, 2) Cut and paste SQL statements into SQL Analyzer and look Exection Plan to get the cost of each process and if there is some statistics missing. From SQL Analyzer you can else run Index Tuning Wizar for that specific SQL. 3) Run Monitor and to find if Proccesor is allways high and/or Average Disk Queue is > 2 for a while, 10 minuts or more. BTW: Did you run Update Statistics?
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
with all due respect this is really more a bug with the application design than with Microsoft SQL Server… 24 table join is never a good idea, nor is a large number of in values… If you don’t have any control over the view definition, then I suspect that it is not likely that you’ll be able to get much of a performance boost… If you can change the view definition, then take the slow running SQL out of a trace and paste it into QA. See if forcing indexes helps you at all… (i.e. compare the indexes used with a smaller number of in values and force those in the view) Cheers
Twan
Agree with Twan about a bug.
But I deal all the time (is my job) whit 3rd party software and is unbelieve how run before and after 3 or 4 month (1 or 2 times a week) of tuning without change any single line.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
]]>