SQL 2012 :: Sleeping Queries Blocking Rebuild Index And Update Statistics Job In AlwaysOn
Feb 3, 2015
At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.
I have to stop update statistics job manually once i come to office manually.
Once I have killed blocking sleeping query but then other sleeping query blocked it and so on.
Is it neccessary to schedule a update statistics on index in sql server 2005 on daily basis Is it neccessary to schedule a rebuild index on index in sql server 2005 on daily basis
This calls the Sp that does the Reindex. It fails at the update statistics with a very generic message. like " Command: UPDATE STATISTICS [xxxx_DB].[dbo].[xxxx_xxx] [_WA_Sys_00000007_49C3F6B7] [SQ... The step failed."
I suspect it has more error but this is all it is showing me when I right click on the job history. therefore, I updated the job step in the advance tab with log to a txt file. Am I on the right track or there is another way to see error some where else.
I looked at the logs but they didn't show any thing.
Over the past week and a half we started experiencing a sporadic slowdown in our production x64 SQL 2005 Ent. Edition server. Users started complaining of slowness then they started getting timeouts. In looking at sp_who2 and perfmon we saw the following during the slow/frozen periods: * Dramatic increase in Perfmon Active Transactions * CPU higher than norm, but not dramatically so * sp_who2 shows a number of spids in SUSPENDED state (and not running waits) * no blocking indicated from sp_who2 * active connections slowly increasing * no disk queuing (or at most some spikes to 1) After a couple of minutes of this we would then see the following: * no more spids in SUSPENDED state * Logins per second spikes dramatically * Active transactions spikes down to "normal levels" * CPU goes high then levels out at moderately higher than normal * active connections slowly decreases back towards normal levels * large spike in lock wait time
We turned on the Async Auto Update Statistics option (after testing in our staging environment) on the primary database about a week before we saw this problem. By turning it off we can visually see the problem go away by watching the above metrics. So my question is, What metrics can I use to see the "blocking" or resouce locking that is causing these problems? Anyone? Thx Ron
We have a 20 GB database and reorganize indexes and update statistics maintainance takes about 4 hours and the log files grows out of control what is a serious problem since it can not be truncated (database mirroring).
We use below OLA script to do our index maintenance and one of our previous engineer designed below script on web edition and I have a question of how online index rebuild works when we have web edition. Does the online Index rebuild really works? I am thinking it only reorganizes and does not do online index rebuild.
We have multiple SQL 2012 SQL servers setup in an alwaysOn availability groups. Where should we schedule the re-index? We have Server1 as the primary and 2 secondaries Server2 and Server3. Are their any tricks to have it run on which ever one is the primary?
We face slow performance issue for like taking long time for same query execution after We apply index rebuild and reorganize index. But, after execution of query or procedure for 2 -3 times, performance will be faster. I have following questions
1 do we need to update stats after we rebuild an reorganize index. 2. is it will be slow for 1-2 times for every query and stored procedure execution after we rebuild and reorganize index?
We have implemented a very small reporting database which has a main table that started off small and has now grown to around half a million rows. Initially, there were no indexes on the table apart from a clustered index, but as the data has grown, performance has dropped and so we have added a number of indexes. This has resolved the performance issues.
Before creating the indexes SQL Server had auto created a number of statistic objects (_WA_Sys_000... etc). After creating the indexes, new statistic objects where created for the new indexes. In some cases, there are duplicate statistics (auto and index) for the same columns.Should I go through and drop the duplicate auto statistics? Will having duplicates cause issues at all?
I am working on an existing infrastructure and i do not have liberty to change much right now. I am in a situation where app issues update statistics command quite often. So frequently that sometimes one blocks another. Is there any way i can do something like this
IF ( update_statistics going on) dont do anything else run update statistics
This is temporary solution untill i fix bad inline SQL code (in app) and use SPs.
My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?
I am using Ola Hallengrens scripts for index and stats maintenance but I am wondering what most people to in terms of the maintenance schedules. At present we do an index rebuild reorg weekly, but do people also do update stats nightly?
I suppose there is an element of "it depends" here in that the data may be fairly static so the update stats may not be required, or if heavily updated then perhaps rebuilding indexes may be required more frequently.
I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.
If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.
If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.
Rebuild Index job for user db's is failing, one user db is a huge size 120 GB. The job scheduled to run every sunday 1 AM
I found the below error in log report
Rebuild Index Task (server name) Rebuild index on Local server connection Databases: All user databases Object: Tables and views Original amount of free space Task start: 01/13/2008 1:26 AM. Task end: 01/13/2008 2:38 AM. Failed-1073548784) Executing the query "ALTER INDEX [Idx_CISCO_WLC_EVENTID] ON [dbo].[CISCO_WLC_200711262137] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF ) " failed with the following error: "Cannot find the object "dbo.CISCO_WLC_200711262137" because it does not exist or you do not have permissions.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hello everyone, I am hoping someone can help me with this problem. Iwill say up front that I am not a SQL Server DBA, I am a developer. Ihave an application that sends about 25 simultaneous queries to a SQLServer 2000 Standard Edition SP4 running on Windows 2000 Server with2.5 GB of memory. About 11 of these queries are over views (all overthe same table) and these queries are all done from JDBC but I am notsure that matters. Anyway, initially I had no problem with thesequeries on the tables and the views with about 4 years of information(I don't know how many rows off hand). Then we changed the tables toreplicated tables from another server and that increased the amount ofdata to 15 years worth and also required a simple inner join on 2columns to another table for those views.Now here is the issue. After times of inactivity or other times duringthe day with enough time between my test query run I get what lookslike blocking behavior on the queries to the views (remember these allgo to the same tables). I run my 25 queries and the 11 view queriesall take about 120 seconds each to return (they all are withinmilliseconds of each other like they all sat there and then werereleased for processing at the same time). The rest of the queries arefine. Now if I turn around and immediately run the 25 queries again,they all come back in a few seconds which is the normal amount of time.Also, if I run a query on one of views first (just one) and then runthe 25 queries they all come back in a few seconds as well.This tells me that some caching must be involved since the times are sodifferent between identical queries but I would expect that one of thequeries would cache and thus take longer but the other 10 would befast, not all block for 2 minutes. What is more puzzling is that thisbehavior didn't occur before where now the only differences are:1) 3 times more data (but that shouldn't cause a difference from 3seconds to 120 and all tables have been through the index wizard with aSQL trace file to recommend indexes)2) There is now a join between 2 tables where there wasn't before3) The tables are replicated throughout the day.I would appreciate any insight into this problem as 120 seconds is waytoo long to wait. Thanks in Advance.Chris
I am running MS SQL 2000 server. The table involved is only about10,000 records. But this is the behavior I am seeing.The local machine is querying the table looking for a particularrecord. Everything works fine.The remote amchine goes to clear the table and then insert all 10,000records, into the table the following happens.1) the local machines queries do not compilete until the remotemachine is done.2) the remote machine can take up to 6 minutes to do these 10,000insert operations. So nothing on the local machine works right forthese 6 minutes.I do not have access to the remote machines source to see what isrunning but I am told it is simply a for loop with a insert query init. Nothing of a locking natture.Any idea the types of things I should look for to track this down? Ifound this by doing SQL profiler profiling and finding the remoteoperations. Turn these operatiiosn off and the local machine worksfine again, with no other action.Thanks,David
We are still testing SQL 2005. We changed all the CREATE INDEX jobs to ALTER INDEX. we ran the index jobs and noticed a lot of blocking. The jobs eventually completed successfully though. The spids were blocking themselves. And this was on and off. I qould query sysprocesses and see the blocking intermittently. I didnt notice this when we run the same indexing jobs in 2000. Is this the way ALTER INDEX works in 2005 or is this something we need to be concerned about? We have build 3186. I need to mention that the jobs finished rather quicker as compared to 2000. And the spids were blocking themselves not other spids.
I have a table with over 60 million rows (approx 20GB) which has an indexed column. I have tried using DBC DBReindex to rebuild the index, but after kicking it off on a friday, it is still running the following wednesday. Since managers and other finicky types access this database, that's not acceptable (it slows down their reporting).
Is there a way to speed up the reindexing process? Perhaps by adding space to the tempdb (it's 500MB) or putting it in RAM temporarily? I haven't seen any articles that specifically state that TEMPDB is used during an index rebuild, but it seems logical that it would be.
Any suggestions to speed up the process would be most appreciated!
