DB Engine :: Index Operation Getting Blocked
Sep 17, 2015
Since couple of days, I am getting a very weird problem on my production environment. Basically, any index operation [rebuild, drop] etc. against an index on a table is getting blocked by [Sleeping,Dormant] connections.
Scenario: I have a Table TableX in database DBX on Server X. This table has a Non-unique, non-clustered index on a NVARCHAR column. This table is accessed by SQL written in a store procedures from Server A via a link server. Both the Servers are SQL 2008 R2 edition.
If I try to run any operation on the index, it get blocked by existing session and keeps on getting blocked by different sessions. Yesterday it was blocked for about 13 hours before I killed the session.
Executing sp_WhoisActive shows few sessions with Host Server A without any sql_text. These sessions actually blocked the indesx operation on the ServerX. These sessions always exist. Sessions details are
Status= Sleeping/Dormant
Open_Tran_Count=1
Host=ServerA
Read/Writes=NULL
CPU=NULL
WaitInfo=NULL
I am not sure how to find the sql causing this issue.
View 4 Replies
ADVERTISEMENT
Apr 6, 2015
This is running on sql server 2012, we have seup the databases on Availability group. The strange behavior i have been seeing the past few weeks is task manager has been blocked from index Re-org but have not found that what that task manager doing.. it won't shows the statement that running but shows as command type= 'Task manager'. Index is so big and i have been stopping this because of triple thread blocking.
View 9 Replies
View Related
Nov 26, 2007
Hi All,I have one question about many "or" operation make system chooseincorrect indexThere is one table TT (C1 VARCHAR(15) NOT NULL,C2 VARCHAR(15) NOT NULL,C3 VARCHAR(15) NOT NULL,C4 VARCHAR(15) NOT NULLC5 VARCHAR2(200),)Primary Key TT_PK (C1, C2, C3, C4)SELECT C1, C2, C3, C4 FROM TT WHERE C1 = 'TEST' AND ((C2 ='07RES' AND C3 = '00000' AND C4 = '02383') OR (C2 = '07RES' ANDC3 = '00000' AND C4 = '02382') OR (C2 = '07RES' AND C3 = '00000'AND C4 = '02381') OR (C2 = '07RES' AND C3 = '00000' AND C4 ='02380') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02379') OR(C2 = '07RES' AND C3 = '00000' AND C4 = '02378') OR (C2 = '07RES'AND C3 = '00000' AND C4 = '02377') OR (C2 = '07RES' AND C3 ='00000' AND C4 = '02376') OR (C2 = '07RES' AND C3 = '00000' ANDC4 = '02375') OR (C2 = '07RES' AND C3 = '00000' AND C4 ='02374') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02373') OR(C2 = '07RES' AND C3 = '00000' AND C4 = '02372').... about 100 or operationsOR (C2 = '07COM' AND C3 = '00000' AND C4 = '00618') OR (C2 ='07COM' AND C3 = '00000' AND C4 = '00617') OR (C2 = '07COM' ANDC3 = '00000' AND C4 = '00616') OR (C2 = '07COM' AND C3 = '00000'AND C4 = '00608') )The system choose index prefix, and query all index leaf withC1='TEST'Prefix: [dbo].[TT].C1 = 'TEST'After I reduce the OR operators to 50, it use choosePrefix: [dbo].[TT].C1, [dbo].[TT].C2,[dbo].[TT].C3,[dbo].[TT].C4='TEST, '07RES', '00000', '02383'Then Merge Join, it is very quick,Can anyone help on this, do I have to reduce the OR operator to 50?Thanks in advance!
View 5 Replies
View Related
Nov 24, 2015
What is the reason behind Index key length limitation upto 900 bytes?
View 10 Replies
View Related
Jul 26, 2015
I have one partition table "tablea" with partition key dateentry on yearly basis and table have four partition with name y2013,y2014,y2013,y2015 with one partition schema . How I can create partition index on tablea that first time create partition index and next time I want to rebuild index only on y2015 partition .
<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>.
View 3 Replies
View Related
Sep 16, 2015
I need to setup log shipping from a 2008 Standard to a 2012 Standard server. I have not found any restrictions for this, however, when I want to connect to the 2012 server on the add secondary server page, I receive the following error:'Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)'.
View 3 Replies
View Related
Oct 31, 2015
Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?
View 6 Replies
View Related
Nov 16, 2007
I have the following table:
Create Table Item(
I_Code NVarChar(40) Primary Key NOT NULL,
I_MatID NVarChar(40),
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1 nvarchar(55),
I_Acquired nvarchar(35),
I_Info2 nvarchar(55),
I_Info3 nvarchar(55),
I_Date DateTime DEFAULT GetDate()
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);
Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )
While this one use:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )
Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423
Some feedback?
Thanks
View 5 Replies
View Related
Nov 4, 2015
Just wonder if system base tables always use clustered index? I am using SQL Server 2005 and find sys.sysidxstats base table is using heap, not clustered index. Why?
View 2 Replies
View Related
Nov 14, 2007
Hello all.
I have the following table
Create Table Item(
I_AssetCode NVarChar(40) Primary Key NOT NULL,
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20)
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
It is populated with 50 000 records.
Searching on indexed columns is fast, but I've run into the following problem:
I need to get all distinct companies in the table.
I've tried with these two queries, but they both are very slow!
1. "select I_Company from item group by I_Company " - This one takes 19 seconds
2. "select distinct(I_Company) from item" -This one takes 29 secons
When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first...
The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time !
Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing!
So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause?
According to SQL CE Books online:
Group By
"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group."
It seems the aggregate is run every time, not only when there is an aggregate function.
Is this a bug?
Thanks in advance,
TipoMan
View 4 Replies
View Related
Apr 28, 2015
On our particular database server, we run the Rebuild Index Task (Using classic Maintenance Plan Designer) every night. Running the script below, I saw that about 77 tables had an avg_fragmentation_in_percentage between 80% and 99% !!
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30--You can specify the percent as you want
ORDER BY indexstats.avg_fragmentation_in_percent DESC
I dont understand why these tables are highly fragmented after a daily index rebuild! Unless the users are doing heavy inserts/updates/deletes during the day.
View 6 Replies
View Related
May 4, 2015
Are there anyway I can check my last database reorg date and time using Tsql ?
View 5 Replies
View Related
Jul 20, 2015
I am currently investigating the best way to handle partition swapping where the partition scheme is built on several file groups. In order to swap partitions, both partitions need to be on the same file group. In addition, the file groups and partitions are automatically updated once a month meaning that there is a scheduled job that adds a new file group, file group files, and updates the related partition objects.If I am forced to create a new non-partition table on the same file group as the partition of the target table, what would be the best way to create the target table without hard coding the full table definition and clustered index? I know that I can query the systables and syscolumns views to reconstruct a basic SQL statement to build the table definition and views Is there a SQL Server function that I can use to just give me the object definitions? Unfortunately, the OBJECT_ DEFINITION function doesn't apply to tables or indexes. URL....
View 4 Replies
View Related
Aug 26, 2015
I am currently re-writing an overnight index defrag procedure and would like to audit indexes in my database - logging the before defrag action" avg fragmentation value and "after defrag action" frag value in an audit table. This will be for all databases on the server. I have completed the vast majority of it (cycling though all the databases, detecting which indexes need reorganising or rebuilding and inserting the information into a table) but I cannot get the audit values working properly. For example, a sample row in my audit table would look like this:
ID Name DB Table frag_before frag_after
1 Index2 DB1 Table6 70.33456 0.03
2 Index7 DB1 Table9 45.98 1.2567etc
View 2 Replies
View Related
Nov 10, 2015
1. I need to make use of in memory engine for my pr-existed develop procedures ,tables ,index. do I need and code changes for application and how to store tables /indexes in OLTP memory
Assume table index may have primary key index as well.
2. If table with one primary index and 2 foreign constraints, 3 non clusters indexed. which one able o load to memory area and how t do that.
3. In memory is lock free zone. usually locks will happpen in RDMS context . how this works without locks.
View 3 Replies
View Related
Apr 26, 2015
We have 3 maintenance jobs configured in this particular DB instance:
Daily backup of system database - SubPlan1 (Check Database Integrity Task --> Rebuild Index Task-->Backup Database Task)Daily backup of user databases - Five subplans for each task : (Check DB integrity --> Rebuild Index -->Backup User Database, Backup Log -->Cleanup History)Weekly maintenance - SubPlan1 (Check Database integrity job (system+user DB) + rebuild index job (system+user DB) )
PROBLEM: I just noticed that the User DB Rebuild Index task has been running since the 03/04 and the Weekly maintenance plan - subplan1 since the 12/04.
Which job is "safe" to stop without impacting the database?
View 14 Replies
View Related
Nov 20, 2013
I'm using SQL Server 2012 Analysis services in Tabular mode and connected to Oracle Database and while importing, I'm getting below error after importing some rows.
OLE DB or ODBC error: Accessor is not a parameter accessor.. The current operation was cancelled because another operation in the transaction failed.
View 1 Replies
View Related
Sep 4, 2015
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
View 4 Replies
View Related
Oct 20, 2006
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
thank you in advance
View 3 Replies
View Related
Dec 7, 1999
Hello...
Is it normal in SQL Server 6.5 the user who only running the query blocking the other user who try to update/add the records?
note: The query is a complex SQL.
Many Thanks!
View 2 Replies
View Related
Mar 17, 1999
We are using SQL server 6.5 and currently have about 100 users connections at a given
point in time. The application is Visual Basic 5.0 based and it allows users to create
MS Word documents from the application. These documents names are stored in a table which basically
acts as a reference table. Every time a document needs to be attached to a record
this table is called with an insert/update query. This results in an exclusive page/table
lock and ends up into a blocked process. This finally results into a major halt for
all the system users.
Manual killing of these blocked processes frees up the resources and brings things back to normal
although disruptive to the users.
Any clue as to why the blocked processes are not able to free themsevles up ?
Are we missing something in our SQL configuration that will help us with unblocking
these processes ?
View 1 Replies
View Related
Feb 27, 2002
Hi,
Issuing 'dbcc shrinkfile (log ,truncateonly)'
I get the error message below. Books online doesn't say any more.
Can anyone explain? I am the only user connected to the db at the time, no jobs are executing.
Cannot shrink log file 2 (log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
View 1 Replies
View Related
Oct 6, 1998
What is SPID -1? I see nothing listing it in any activity monitor, but it has just recently started blocking some processes.
Thanks,
jim craddock
View 3 Replies
View Related
Apr 5, 2006
I'm running a heavy SELECT query using WITH (NOLOCK). This still causes other processes trying to INSERT in one of the tables to get blocked.
I thought the locking hint would prevent from blocking other processes?
View 4 Replies
View Related
Jun 29, 2006
I am getting the following error message when I tried installing the 2005 Developer Edition from the MSDN kit. I previously installed the 2005 Express Edition, and it worked just fine, so I thought that the install failed as it was at a lower version than the Express Edition. I uninstalled the SQLExpress Edition, however, when I try to install SQL Server 2005 again, I get the following message:
Name: Microsoft SQL Server 2005 Tools
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
Name: Microsoft SQL Server 2005 Tools
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
I have also installed Visual Studio NET, and I wonder if some component in the Studio app is holding back the SQL Server install. I have uninstalled all SQL components several times already, and I keep on getting this message.
Gregory
Gregory
View 1 Replies
View Related
May 4, 2008
Hi all,
I have an SQL Server 2005 mirroring config with "High availability". Twice over the past four weeks, the principal server started logging to the event log:
"All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT" I checked the database activity monitor and indeed there were lots of processes in DBMIRROR_DBM_EVENT.
The database was totally unresponsive to queries (all got timeouts), and no failover was done.
This are the log entries for all day:
05/04/2008 08:40:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:39:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:38:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:37:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:36:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:35:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:33:49,Server,Unknown,The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
05/04/2008 03:24:02,Backup,Unknown,Log was backed up. Database: TeleCable<c/> creation date(time): 2007/08/24(13:38:00)<c/> first LSN: 205089:18926:1<c/> last LSN: 209688:18463:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'\face01f$logbackup'}). This is an informational message only. No user action is required.
05/04/2008 00:00:14,spid24s,Unknown,This instance of SQL Server has been using a process ID of 1700 since 4/9/2008 3:18:54 AM (local) 4/9/2008 1:18:54 AM (UTC). This is an informational message only; no user action is required.
After that, the same entry once per minute.
I restarted the database and everything came to normal.
As you can imagine, the problem is really serious.
Should I forget about mirroring? Anyone has experienced something similar?
Thanks in advance and best regards.
View 11 Replies
View Related
Jun 10, 2004
Hi guys,
I have a stored procedure which generates the next sequence number...
it uses SERIALIZABLE Option. procs look something like below..
begin
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
Sequence generating statement...
COMMIT TRANSACTION
set @NextSequenceValue = @NextSequenceValue
Return @NextSequenceValue
end
For some reason when i call the proc with below parameters to get next sequence number.. its hungs up..
declare @NextSequenceValue int
set @NextSequenceValue = 0
exec spGetNextSequence 19, 'LotSequence', @NextSequenceValue output, Null
select @NextSequenceValue as NextSequenceValue
When i queried sp_who2 it shows that my processid is blocked by some other processid.. and when i do DBCC INPUTBUFFER (blockingprocessid), the query of blocking processid and my nextsequence generation stored proc is not realted at all..
Can you help shed some light on why my nextsequence generating proc is getting hunged...?
help is appericated..
View 4 Replies
View Related
Jul 21, 1999
If I kill a blocked process, why does the current activity window still show the process? Both processes, blocking and blocked, are scheduled tasks. Also, the blocked process is still listed as a running task in the manage scheduled task window.
View 1 Replies
View Related
May 10, 1999
The following is an extract from sp_who2
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO
----- ---------- ------- -------- ----- ------- ------- ------- ------
162 sleeping om18682 . -1 STIPROD SELECT 236 120
Has anybody experienced a process being blocked by SPID -1.
View 4 Replies
View Related
Oct 18, 2007
Hi,
I have a fairly complex application running which has numerous connections on various threads accessing my sql 2005 database.
Every now and then I find that one of my connections is being blocked by process with a pid of -2.
I assume this is some system process.
The only way I can get my application going again is to restart the sql server.
Can someone tell me what process -2 is?
cheers,
Robert
View 2 Replies
View Related
Sep 25, 2006
HiShortly, I keep invoices in a table.Occasionally, someone will fire the execution of a stored procedure(SP) that performs several UPDATEs against (potentially) all invoicesOLDER than a date that is supplied to the SP as a parameter.The SP is usually a lengthy process (it takes at least 30 mins).The problem is that SQL server 2000 Dev Edition doesn't allow me toinsert new invoices that are "younger", while the SP is executing.How should I specify to SQL Server that new invoices are "harmless"?Thanks.
View 8 Replies
View Related
Jul 20, 2005
I have written a stored procedure to list out all tables in whichrows or the table itself is locked. The only information I amnot able to get is the time when the lock occurred. The way Iwant is that if I run the procedure it should show all lockson a table which are at least 5 or x seconds old. This way I canavoid momentary locks on a table which go away after few seconds.Which table and column of master database has that information?Thanks.--email id is bogus
View 3 Replies
View Related
Oct 11, 2006
Hello,
I am trying to execute next query, but when doing it, TABLE1 locks and it does not finish.
SERVER2 is a linked server.
BEGIN TRAN
INSERT INTO TABLE1
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2 WHERE TAB_F1 IS NULL
COMMIT TRAN
I have same configuration in other 2 computers and it works ok.
What is the problem?
Thank you!!
View 8 Replies
View Related
