March 27, 2012 at 12:07 pm
I am an app developer who writes queries. My DBA has added an index to a table and I want to compare queries with and without the index. Is there a way to do this?
March 27, 2012 at 1:29 pm
Is it a clustered index, or nonclustered?
If nonclustered a simple way requiring no knowledge of the index definition:
ALTER INDEX index_name ON schema_name.table_name DISABLE ;
-- Rebuilding the index re-enables it
ALTER INDEX index_name ON schema_name.table_name REBUILD;
If it's a clustered index disabling it will prevent all access to the table, and you don't want that. In this case you'll have to drop the index and recreate it. The recommendation on removing and re-adding clustered indexes is to drop all nonclustered indexes first, then drop the clustered index, then re-add the clustered index and finally re-add all nonclustered indexes. I would use SSMS to script the DROP and CREATE index statements to a new window and organize the SQL for all the steps I mentioned before getting started.
If it's a large table we're talking about, either method could take a while.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2012 at 1:43 pm
i know you can force a query to use a specific index, would that help?
SELECT ContactID
FROM Person.Contact WITH (INDEX(AK_Contact_rowguid))
Lowell
March 27, 2012 at 1:49 pm
I could have misread, but I took it to mean the OP needs something like:
SELECT ContactID
FROM Person.Contact WITHOUT (INDEX(AK_Contact_rowguid))
which, oddly, yields no results from Bing 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2012 at 2:13 pm
Right. I was hoping for a "WITHOUT" or "IGNORE" kind of option (Google showed me that MySQL has an IGNORE option). That would let me more directly compare the effect that the index has on the query.
My DBA chose to create the index backwards from the way that I expected it. I have a table like
CREATE TABLE MyTable (ID int, other_system_id int, other_system_key char(20))
where there are 6 other systems, each with 20,000 keys
I asked for an index on other_system_key, but I was told that a better index is
CREATE NONCLUSTERED INDEX other_systems on MyTable(
other_system_id ASC
)
INCLUDE (other_system_key)
This seemed odd to me (I would have expected the columns to be reversed), so I wanted to be able to compare queries with and without it.
Amazingly, I found that I appear to have authority to create indexes on my database, so I created the index that I expected:
CREATE NONCLUSTERED INDEX other_systems on MyTable(
other_system_key ASC
)
INCLUDE (other_system_id)
I also found that in SSMS, I could go to the index properties and select or deselect the "use index" option. Deselecting the first index and rerunning my query shows that the first index is not being (based on the execution plan). The weird thing to me is that both indexes result in a query that completes in the same amount of time.
March 27, 2012 at 2:20 pm
this index:
CREATE NONCLUSTERED INDEX other_systems on MyTable(
other_system_id ASC
)
INCLUDE (other_system_key)
would be good for qa query like
SELECT
other_system_id,
other_system_key
FROM MyTable
WHERE other_system_id = 42
it would not benefit a query that said WHERE other_system_key = 'xmpl';
if the WHERE statement is going to be on other_system_key, you would want an index on that column...just as you were expecting.
Lowell
March 28, 2012 at 11:27 am
opc.three (3/27/2012)
ALTER INDEX index_name ON schema_name.table_name DISABLE ;-- Rebuilding the index re-enables it
ALTER INDEX index_name ON schema_name.table_name REBUILD;
How long should disable and rebuild take? In SSMS, they appear to be instant.
I ask because as I attempted to compare the value of two indexes, I ran it as follows:
--test1: both indexes off
ALTER INDEX index_name1 ON schema_name.table_name DISABLE
ALTER INDEX index_name2 ON schema_name.table_name DISABLE
--run test
--test2: both indexes on
ALTER INDEX index_name1 ON schema_name.table_name REBUILD
ALTER INDEX index_name2 ON schema_name.table_name REBUILD
--run test
--test3: first index only
ALTER INDEX index_name1 ON schema_name.table_name REBUILD
ALTER INDEX index_name2 ON schema_name.table_name DISABLE
--run test
--test4: second index only
ALTER INDEX index_name1 ON schema_name.table_name DISABLE
ALTER INDEX index_name2 ON schema_name.table_name REBUILD
--run test
the test run is the query that needs the index run against 10% my development database. I am then getting average, minimum, maximum, and total run time.
as expected, the first unindexed test run was way slower than the subsequent 3 indexed runs. However if I reran the first test after the three indexed tests, the timing was significantly faster.
Is there something that I need to do to ensure that the Disable and Rebuild are complete and taken into account?
March 28, 2012 at 11:48 am
Is there something that I need to do to ensure that the Disable and Rebuild are complete and taken into account?
The commands are synchronous so they will not return until complete. If there is not much data it may not take long to rebuild the index. The disable should return almost instantly no matter how large the index.
If you want to check though, you can look at the sys.indexes.is_disabled column.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply