There are many possible explanations for this. It may be that you have to accept it as the cost of making an improvement but it may be highlighting issues elsewhere in the system. Possible causes:
- Fragmentation of the table.
- Fragmentation of the physical database files.
- Poor choice of clustered index key.
- Large table with low percentage of its data in the buffer pool, requiring lots of physical IO to read.
- Lack of spare memory/io/cpu capacity to deal with this one off task on top of the normal workload.
- Index creation requiring tempdb for sorting but tempdb on same drive as data and or log files.
Your question is a little sparse on the details necessary to provide a conclusive answer. To start with, can you add the table DDL (including clustered index definition) to your question along with row counts and database size. Also, a description of the server spec (memory, cpu, drive configuration) and whether or not tempdb is on a separate array.
Next, the following will give us an indication of how your IO subsystem is performing.
SELECT
DB_NAME(fs.database_id) AS [Database Name]
, mf.physical_name
, io_stall_read_ms
, num_of_reads
, CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
, io_stall_write_ms
, num_of_writes
, CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
, io_stall_read_ms + io_stall_write_ms AS [io_stalls]
, num_of_reads + num_of_writes AS [total_io]
, CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads
+ num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM
sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN
sys.master_files AS mf
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY
avg_io_stall_ms DESC
OPTION
(RECOMPILE) ;
The following will tell us how badly fragmented your existing indexes are. You may want to run this at a quiet time as it can impact on performance.
SELECT
DB_NAME(database_id) AS [Database Name]
, OBJECT_NAME(ps.OBJECT_ID) AS [Object Name]
, i.name AS [Index Name]
, ps.index_id
, index_type_desc
, avg_fragmentation_in_percent
, fragment_count
, page_count
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
INNER JOIN
sys.indexes AS i
ON ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
WHERE
database_id = DB_ID()
AND page_count > 500
ORDER BY
avg_fragmentation_in_percent DESC ;
Best Answer
In short: No.
There will be a very very small difference in parsing time for statements that specifically mention the index or generating output that mention the index, but this is so vanishingly small compared to all the other work that teh database engine it doing that it is simply noise - far too small to even reliably measure.
When the index name is not explicitly used then even that difference is gone: the index will be looked up by the columns it contains data for not by its name. If the engine needs to refer to he index directly it will do so by some internal ID - the name you know is essentially just for us humans.
Baring that in mind, especially the second point, long descriptive names are recommended. There is a caveat there though: if you make the name too long then it becomes unwieldy, and many tools will impose arbitrary limits on the length of identifiers. SQL Server for instance limits all names to 128 characters (if you get close to this limit you have probably gone too far!) but I've seen some tools (that were bad in other ways too...) fall over when they hit anything longer than 50. The mySQL documentation should tell you what the internal name length limits are for your circumstance.