Query issues on tables where we altered column data types that were/are uses in indexes.

Question

Query issues on tables where we altered column data types that were/are uses in indexes.

gerbowers

SSC Journeyman

Points: 86
More actions
September 14, 2010 at 3:29 am

#227657

Hi,
We made some changes to our database over the weekend where we altered tables columns where we were using nvarchar to varchar.
We carried out the following steps;
1. Dropped indexes/constraints on revenent columns
2. Altered column datatypes
3. Rebuilt indexes and constraints
4. Ran optimization plan on database.
Our problem now is that some of our queries are not behaving and are taking long time to run. The queries are not using any of indexes that were re-built on the altered columns.
We have issued a recompile on all of our database object, which I thought would have recreated a query plan.
I am not looking at the possibility that this is something to do with the statistics, am I barking up the wrong tree or should I look somewhere else to see if I can identify the problem.
Do we need to re

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

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

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

As a wild guess, all your query/stored proc parameters are still nvarchar? If so, you're forcing SQL to implicitly convert the columns to nvarchar to do the comparisons. That prevents any index usage.

Check the exec plan, see if you see CONVERT_IMPLICIT on the columns. If so, the parameter types need fixing to match the columns.

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

gerbowers SSC Journeyman Points: 86 More actions · Answer 2

No thats not it, as part of the exercise we made all thye proc parameters exactly the same as the table column types.

In looking at the execution plan its not using one of the columns that we altered.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Can you post an exec plan of one of the misbehaving queries?

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

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 4

Rebuilding the indexes should have also rebuilt the statistics, so I wouldn't necessarily assume that's where the problem lies. Without seeing the queries & execution plans it's hard to know what the problem might be. You could try updating the statistics with a full scan on the tables that are giving you troubles, but that's a SWAG.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning