Home / server / Questions / 263685
Accepted
Matt Dawdy
Asked: 2011-04-27 13:54:40 +0800 CST

Add index to very large SQL Server table

  • 772

I've got a table with 9 million rows (just received it from our underwriting provider). 4 columns -- leadid (guid), RawLeadXML (nvarchar(max) that contain an xml doc in each row that is 2-3kb), LeadStatusID (int), and id (autonumber int, I just added this).

I am unable to run even a simple delete statement.

DELETE FROM Leads WHERE LeadID = '100a7927-5311-4f12-8fe3-95c079d32dd4'

I have tried to add an index to LeadID, but that times out.

CREATE NONCLUSTERED INDEX IX_Leads ON dbo.Leads(LeadID) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

My machine has 4 processors and 12 GB of RAM. Running Windows 7. SQL Server 2008 (I believe Developer Edition). How can I add an index to this table?

sql-server-2008 sql indexing

1 Answers

  1. Best Answer

    9M rows is not really that many. My guess is that in both situations, something is locking the table.

    Check the table locks, and either close any processes that are locking the table, or kill the locks manually (if you're sure nobody else is using the database). There's a reasonably good script to show all locks here.

    SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id
    • 1