July 26, 2011 at 6:55 am
Hi guys
I have a database with about 2 million records, originally all the data types were nvarch(255) but they have now changed to integer, big integers, small integers, nvarchar and date/time data types. Before I changed the data types the results from a select queries appeared in seconds but now I have changed the data types the results can take about 30 seconds to return the results.
I have run the database turning wizard tool but it does not advise on indexes to apply (it already has indexes)
Is there anything I can do to improve the speed? All the queries are Select stored procedures :-):-):-):-)
July 26, 2011 at 7:04 am
My first guess would be implicit converts... nullifying the indexes.
Can you post the query, table ddl, index & keys DDL along with the actual execution plan (not estimated).
July 26, 2011 at 7:05 am
have a look at the acutal exection plan on the slow running query and this will provide you with the information you need. Post it here if you need any help interpreting it
July 26, 2011 at 7:34 am
hello,
in profiler you can use StmtCompleted under stored procedure section to find out the time taken and based on that you can action it.
Regards
Durai Nagarajan
July 26, 2011 at 7:37 am
durai nagarajan (7/26/2011)
hello,in profiler you can use StmtCompleted under stored procedure section to find out the time taken and based on that you can action it.
Surely you need more information than the time completed?
as they already know it is slow
July 27, 2011 at 4:57 am
i thought the SP may have several lines of code, only few may causing the issue which may be easy to find(index or other).
do you think my approach is wrong in this.
Regards
Durai Nagarajan
July 27, 2011 at 5:16 am
durai nagarajan (7/27/2011)
i thought the SP may have several lines of code, only few may causing the issue which may be easy to find(index or other).do you think my approach is wrong in this.
I wouldn't say it is wrong asit will provide information that can be useful, but i always feel the best place to start with performance tuning is the (actual) execution plan and then work down from there.
July 28, 2011 at 5:53 am
1) Ninja hit it, and I doubt we need query plans to verify it. Your original code was set up to use nvarchar datatypes or joins and now you are hitting 'proper' datatypes that are causing CONVERT_IMPLICIT to get to the right datatype which not only voids index seeks but also gets you bad estimates too.
2) you may also have created problems if you converted columns on different tables that are the same column and used for joins to different datatypes, getting the same problem.
3) BEWARE DTA!! It does numerous bad things with indexing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 28, 2011 at 6:01 am
TheSQLGuru (7/28/2011)
3) BEWARE DTA!! It does numerous bad things with indexing.
I've actually had DTA CRUSH the server's CPU (large load) and then fill up the drive by growing msdb.
On top of proposing dangerously large indexes... which makes it easy to slow down the server if you don't know what you are doing.
If you are using DTA and msdb is getting fat, you might want to consider deleting your projects, that'll save space and then you can shrink msdb back down to a reasonable size.
Yes I said shrink, this is ONE case where it can be appropriate. 😉
July 28, 2011 at 3:12 pm
Ninja's_RGR'us (7/28/2011)
TheSQLGuru (7/28/2011)
3) BEWARE DTA!! It does numerous bad things with indexing.I've actually had DTA CRUSH the server's CPU (large load) and then fill up the drive by growing msdb.
On top of proposing dangerously large indexes... which makes it easy to slow down the server if you don't know what you are doing.
If you are using DTA and msdb is getting fat, you might want to consider deleting your projects, that'll save space and then you can shrink msdb back down to a reasonable size.
Yes I said shrink, this is ONE case where it can be appropriate. 😉
I don't use DTA to analyze a workload on a production server, because it's too resource intensive, and nor would I use to even alayze all activity on a database in development, because it's just too much stuff to sort through. However, I do sometimes use that feature where we can highlight a single SELECT statement or code block in a SSMS Query Window, right click, and then choose 'Analyze Query in DTA'. Of course, the reccomendations have to be taken with a grain of salt, especially when it suggests including a dozen or more columns with a new index. I'll sometimes take one or two reccomendations and use them as a starting point.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply