Forum Discussion
Indexing question
Hey, Will.
I'm not sure I've interpreted everything correctly, but I've got to start somewhere so here goes.
Firstly, if this table is part of a larger system then you most definitely don't want to mess around with the clustered index on the primary key. That will cause performance issues elsewhere - you can almost guarantee it since any foreign key references back to this table will be referring to the primary key.
If the table is more isolated, meaning few foreign key dependencies on its primary key and infrequently featuring in JOIN-oriented queries that use the primary key in the "ON" clause, then you may get away with changing the clustered key from the current primary key to the ManufacturerMarking, but I'd be very surprised if this is the case.
Just to be clear, dropping an index will not delete your data. It will probably kill performance until you re-create some meaningful replacement but you won't lose your data. I do prefer using T-SQL for DROP commands since you really have to not be paying attention to type something like "DROP TABLE" when you mean "DROP INDEX". Even if you type "DROP INDEX <yourTableName>", that's simply going to throw an error and fail. But if the GUI is your preferred approach, just be careful you're right-clicking the right thing!
That's the "easy" advice.
With respect to the rest, the first thing to check is that a non-clustered index exists for just the ManufacturerMarking column - no extra columns included (that's assuming you're not using a WHERE clause to filter the table's rows). I have to point out that I'm going with "non-clustered" as a best guess on how the wider system might hang together, as we only have a small window into the bigger picture here so far.
On the surface, what you've described does meet some of the criteria outlined here for a clustered index but as I just mentioned, if it truly is the beating heart of many relationships then I'd be willing to bet the data should be kept in the ordering it currently is. And this speaks to one key difference between clustered and non-clustered: clustered indexes result in the row data being shifted around so that each index key is stored sequentially.
Hypothetically, if nothing else has formal relationships with this table and you can use a clustered index, then dropping the existing clustered index and creating a new one against the ManufacturerMarking would mean:
- The data/rows wold be stored in alphabetical order based on the ManufacturerMarking column;
- You would no longer need an ORDER BY clause in your view definition since ordering would no longer be necessary.
But again - and I keep coming back to this - I'd be surprised if the table is really that isolated. Only you can figure that out through inspecting the dependencies (things like foreign keys, views, stored procedures, functions, etc.)
Getting back on track, if you have got an existing non-clustered index targeting only the ManufacturerMarking column, then there may well be not much more you can do - particularly as you said it's a very wide table (makes me wonder if you can't use a view to cut down the columns, which will help). But you can run the following superficial T-SQL check just to get some certainty around whether the bottleneck is definitely in SQL, or external to SQL.
If these two queries complete in roughly the same time, then your issue isn't the SQL instance at all.
Let the schema name be "dbo" and table name be "Products", since I don't know either (and it's not important that I do):
-- Query 1
SELECT COUNT(*) AS [Count] FROM dbo.Products AS [p] WITH (nolock)
GO
-- Query 2
SELECT COUNT(*) AS [Count] FROM dbo.Products AS [p] WITH (nolock) ORDER BY p.ManufacturerMarking
If query 2 still takes around 7 second then the bottleneck is definitely within the SQL instance, and at this stage based on what we know, I don't have any better suggestions other than the generalised indexing overview above.