Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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
share|improve this answer
    
I'll definitely check this out. Thanks for the advice. –  Matt Dawdy Apr 27 '11 at 3:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.