Recently a production server suffered a critical blocking period and I wanted to know if I could solicit some input. It seems that a stored procedure was in the middle of recompiling while and auto update statistics started. This caused blocking for like an hour on the
single object (stored procedure) that was originally called. The table that the update occurred on and that the
stored procedure is reading form is quite large. It is 2 mil rows and about 140 columns wide. Some info from
sysprocesses is below. The table alone takes up almost 4GB of space, when looking at sp_spaceused. I have some
questions.
1. Can the update statistics for a '_WA%' stats cause
blocking on a table?
2. Does an update stats on an index survive a restart of
SQL server? We tried restarting, but the blocking did not
end.
3. If the stored procedure is running under a compile, can
the server automatically start an update stats and cause
the stored procedure to wait?
4. Can the server automatically start an update stats on
more than one column stats at a time, causing one to be
blocked by the other?
5. We had never seen this issue before going to SQL2K
clustering. Is this something specific to SQL2K and not
SQL7 ?
Thanks for your input.
John Lee
This is the lock info for the blocking processes.
spid dbid ObjId IndId Type Resource Mode Status name
------ ------ ----------- ------ ---- ---------------- -------- ------ -------------------------
142 7 2 1 KEY (6f00035ef42b) S GRANT sysindexes
142 7 2 1 KEY (6f00035ef42b) S GRANT sysindexes
142 7 421576540 0 TAB Sch-S GRANT tJob
142 7 1141579105 0 TAB Sch-S GRANT tPatient_info
142 7 1141579105 0 TAB [UPD-STATS] Sch-M GRANT tPatient_info
142 7 1659921035 0 TAB [COMPILE] X GRANT iDBGetPatInfoRecord
142 7 1659921035 0 TAB Sch-S GRANT iDBGetPatInfoRecord
These are the processes that are being blocked:
spid
------
137
140
Below this is a snapshot of all the SQL processes on the server being blocked.
Save the report and send to the whole database group.
Does anyone know how to tell how long it took for an auto update statistics to run? I looked under DBCC Show_Statistics and it shows the time the stats were last updated, but not how long it took to update them. Thanks.
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
I've been put in charge of a SQL server recently and I'm completely new to maintaining SQL. For some reason several clients when they are doing their database processing I get network_io hangs and everything comes to a halt. I have to go in there and manually kill the SPID and everything continues where it left off.
SQL Setup
Server 2012 R2 SQL 2014 SP1 Dell 850 VM running in Hyper-V 50GB RAM FusionIO Card 10GB Ethernet between clients and server
All workstations are pretty much
Windows 7/8.1 10GB 64+GB of RAM Several CPUs SSD and SATA hard drives
It's very hit or miss when this occurs as well, however, it's always the same NETWORK_IO cause.
I've read on the Internet about how the client keep up with what SQL is sending. Could this be poor programming or do I need to investigate my network setup?
Hi all, I have an dtsx (SSIS) for "clone" manually Sql server database to another.
How I copy all stats from one database to another ? I have problem with "auto stats".
When I try DROP statitics for auto stats I get this error:
No se puede DROP el Ãndice 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'. No es una colección de estadÃsticas.
Cannot DROP index 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'. Not statitics collection.
What can I do ??
-- Get Stats list SELECT '[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS [Table_Name_With_Schema], '[' + st.name + ']' AS [Name], '' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + '' + '.' + st.name + '' AS [Estadistica] FROM sys.tables AS tbl INNER JOIN sys.stats st ON st.object_id=tbl.object_id ORDER BY [Table_Name_With_Schema] ASC,[Name] ASC
Thanks in advance, any help will be appreciated, regards, greetings
Hi all, I have an dtsx (SSIS) for "clone" manually Sql server database to another.
How I copy all stats from one database to another ? I have problem with "auto stats".
When I try DROP statitics for auto stats I get this error:
No se puede DROP el Ãndice 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'. No es una colección de estadÃsticas.
Cannot DROP index 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'. Not statitics collection.
What can I do ??
-- Get Stats list SELECT '[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS [Table_Name_With_Schema], '[' + st.name + ']' AS [Name], '' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + '' + '.' + st.name + '' AS [Estadistica] FROM sys.tables AS tbl INNER JOIN sys.stats st ON st.object_id=tbl.object_id ORDER BY [Table_Name_With_Schema] ASC,[Name] ASC
Thanks in advance, any help will be appreciated, regards, greetings
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
--Created an insert table DECLARE @COUNT INT
SET @COUNT = 1
WHILE @COUNT <= 1000
begin
insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)
VALUES (@COUNT, '12345678901234567890')
SET @COUNT = @COUNT + 1
END
Look at Tables then statistics the statistics are empty so i fire update statistics and see 1000 rows in here.
I run again the insert script DECLARE @COUNT INT
SET @COUNT = 1001
WHILE @COUNT <= 2000
begin
insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)
VALUES (@COUNT, '12345678901234567890')
SET @COUNT = @COUNT + 1
END
Look again at statistics it not firing.
If i do select * from CUSTOMER where CUSTOMER_ID = '2000' then go checks statictics it works.
I was under the impression that when you do insert or delete, update then the statistics are fired.
The sys.sysindexes rowmodctr shows the 1000 rows.
I checked the conditions that sql fires if the no of rows int able > 6 and < 500 then updates when 500 mods made. Also if row > 500 auto update done when 500 = 20% are added
So both are met.
Anyone other any other suggestions about the auto stats ?
I have a sql snippet from a 3rd party application that will not complete its transaction. The SELECT statement executes but does not finish. Instead the statement just sits in AWAITING COMMAND for 1000 seconds then dies, thus killing the UPDATE statement that is supposed to follow.
With SQL2005 SP2, we are seeing that when auto stats run on one or more indexes of a large table (1.5M rows), then immediately the stored proc using that table starts acting as if the query plan is no longer any good. This causes a drastic slowdown in response time and a corresponding increase of table reads to get the data. E.g, the next execution of the procedure after the auto stats kick in goes from 355 reads to 755000 reads (as depicted by Profiler). Generally, there are about 25 people using the DB at any one time. They connect through a mid-tier VB component.
I tried adding WITH RECOMPILE to the stored proc in question, but that caused almost all executions to run at the higher number. I thought that the WITH RECOMPILE hint would create a new query plan for each execution of the procedure and that plan would the the latest and greatest. Perhaps it did, but most users got stuck with the higher number of reads anyway. After taking the hint out, everyone went back to getting the 335 number and quick response times.
What we are wrestling with is that when those auto stats hit, it really messes up everyone until we manually recompile the procedure. Daily we delete all records in the table that are over 45 days old, so the table stays pretty much the same size. We also set the recompile flag to cause a new plan to be generated that will reflect the smaller amount of data. Should we also run a stats update before recompiling the procedure? Profiler has been very helpful in capturing what is going on, so I think I have a good handle on that. However, I don't understand why WITH RECOMPILE produced a messed up plan for everyone. The compile itself seems to take only 1 ms when done from the query screen.
Has anyone noticed a performance improvement during trading hours when they replaced sp_updatestats with UPDATE STATISTICS FULLSCAN in their nightly maintenance? Or is it negligible?
I'm fairly new to SQL Server and I'm just wondering if it's possible to Update Statistice for all indexes somehow? I'm looking at the Update Statistics command and it doesn't seem to be possible.
The situation we have is a reporting DB that basically has all it's tables truncated and remade every night by some DTS jobs that import from another datasource and change the data and build some denormalzed tables etc. Some of the large Insert operations go from taking 8 mins to taking several hours sometimes and updating the stats seems to fix the problem for a while. So I'd like to make sure the optimizer has all the latest stats for all tables.
I am having the following errors with the script below
Msg 102, Level 15, State 1, Line 44 Incorrect syntax near '?'. Msg 319, Level 15, State 1, Line 47
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. it also does not seem to loop around each db
Recently we moved few of our databases from SQL 2000 to SQL 2005 (SP 2) using backup and restore. After the restore I did Reindex and update stats on the databases. Since then we have observed performance issues on SQL 2005 databases but this performance problem vanishes the moment we run (sp_updatestats). Is this a problem with SQL 2005 that we have to run sp_updatestats 2 times a days or 3 times a day. In SQL 2000 we ran it only Once a week and still we never had any performance issues. Is there any config change we need to do to fix this problem in SQL 2005?
(Assuming SQL Server 2000, auto create statistics on, auto updatestatistics on.)DoesDBCC DBREINDEX(<tablename>)update statistics? If yes, are the statistics equivalent to thosethat would be produced by:UPDATE STATISTICS <tablename> WITH FULLSCAN
Microsoft states that dbcc DBREINDEX automatically updates statistics but INDEXDEFRAG does not. If this is the case, does MS mean that only the affected statistics are updated or all statistics? Also, is it a good idea to run 'Update Statistics' after doing INDEXDEFRAG?
How do i update the stats of tables when we insert data into it. I believe Auto stats update happens only when 500+ 20% of the rows are changed for a table. Once we insert say some 1000 records in to a particular table the query time takes too long (more than 1 min). The same query executes faster once i manually update the stats.
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.
We have a file import job. This job typically imports millions of records into a SQL2008 DB. After the load the DB performance goes down the drain. Thus far, their solution has been to rebuild indexes on effected tables. I'd like to come up with a better solution. My guess is that after the load, the statistics are shot until the next stats update.
What is the best way to handle this scenario? There must be some way to keep the stats current during a big data load.
We are planning to standardize our newly deployed sql server, As a part of it we have configured 2 maintenance plans 1) Update Statistics which runs daily and 2) Index Reorganize which runs on weekly.
Apart from above, any other things to be in place for better maintenance of the sql server.
Also, how to Index Rebuild activity for clustered indexes requires any downtime.
When I checked the Properties of the Statistic I can see it is on a varchar(3) field when there are only 3 different values in there - all char(1)
The total size of the data in the table according to the Disk Usage By Top Table Report is 199,680,712KB
So my question is this...
For the UPDATE STATS on this one column with FULL SCAN, does SQL Server read the entire table into the Buffer Pool. If so then if the table had 199,680,712KB of data then why did the session request 145,705,216KB.
Or does SQL Server just read the column and ClusteredIndex Key into the Buffer Pool?
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.
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 recently migrated quite a databases around 20 from SQL 2000 and 2005 to SQL server 2008R2.
I am using Ola's script for index maintenance for those with compatibility level above 80 as i heard it supports that way.
Hence separated in 2 way job where for those with compatibility level 80, we are running job with below query on each database with 80 as compared
USE ABC GO EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO EXEC sp_updatestats GO
I am not sure if this is the only way in for those databases, because we are seeing the database getting because of that somewhere using above query.( seems log file filling very rapidly).
But above is not the case with those databases , with compatibility 90 and above.
Thought I would post here in case anybody can give some information.
Here is the background information:
I have 2 tables (stores and sales) from the Pubs database in Sql Server 2000 copied down to a SQL Server CE database. There is no foreign key/primary key relationship between the 2 tables in the CE database.
Here are the update queries that cause the error:
UPDATE st SET st.zip = 66668 FROM stores st INNER JOIN sales sa ON st.stor_id = sa.stor_id AND st.stor_id = 6380
Update stores SET stores.zip = 55555 FROM sales, stores WHERE stores.stor_id = 6380 AND stores.stor_id = sales.stor_id
Here is the error message that is generated when I run the query (Param 0 and Param 1 change according to what column and line the FROM clause is in):
Error: 0x80040e14 DB_E_ERRORSINCOMMAND Native Error: (25501) Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,] Interface defining error: IID_ICommand Param. 0: 2 Param. 1: 1 Param. 2: 0 Param. 3: FROM Param. 4: Param. 5:
I ran the 2 queries in SQL Query Analyzer in SQL Server 2000 and they worked just fine. I also created 2 new tables (stores1 and sales1) in SQL Server 2000 using the Select Into clause. The new tables were created from the sales table and stores table in the Pubs database. The new tables had no foreign key/primary key relationship.
I ran the queries again in Query Analyzer against the new tables and the queries produced no errors.
Hi, I'm taking an Excel spreadsheet (that could have around 30k rows) and processing it in SSIS. I essentially have a flag in one of the spreadsheet cols that indicates whether the record is already in the database or not.
I'm splitting the data using a conditional split on this column and using a OLE DB Destination (Fast Load) to perform the inserts and a OLE DB Command to fire a stored procedure to perform any updates. Both the OLE DB Destination and the stored procedure are hitting the same table and the two operations could be executing at the same time as they both appear directly after the Conditional Split, so the OLE DB Destination is set NOT to lock the table.
This seemed to work OK until recently. I've just added 2 triggers onto the table in question which I don't want to fire 30,000 times during the import. As the OLE DB Destination is set to use Fast Load, it doesn't fire the triggers - cool. In the update stored procedure it disables the trigger before performing it's update and re-enables the trigger when finished. Currently this does mean that if you only had updates, the trigger could be enables/disabled 30,000 times. That sounds kinda bad, but I don't really know if this carries a large overhead or not?
If, when importing now you have both updates and inserts the whole process locks up. From looking at activity monitor, it seems as though the INSERT gets suspended.
Do I have a fundamental problem with how I've structured the Data Flow or am I just being really stupid in Enabling/Disabling a trigger that many times, which is probably causing the problem?
SQL Server 2000 Enterprise EditionAccess 2000 Front EndOne of our clients has recently been experiencing problems with an appthat has run satisfactorily (though slowly) for some time. Toovercome the slowness, they have installed a new server with SQLServer 2000 Enterprise Edition with 'Log Shipping' enabled (to providea subsidiary database on which reports can be run) but although thespeed issue is resolved, there are hitherto unencountered blocking andlocking issues when running updates on a particular table (updatestime out, typically when running on > 3 client machines, but don'twhen running on < 3 client machines).Having exhausted most possibilities, we wondered if it was a licensingissue, so we fired up the License Server.This appears to be trying to tell us that SQL Server 7.0 was installedsometime in July. There is no mention made of SQL 2000.In fact, the machine in question has never had SQL Server 7.0installed, and SQL 2000 was installed earlier this month, not in July.Anyone any ideas? For what it's worth, I have scripted the table andthe blocking stored procedures below.Many thanks in advanceEdward/* Table *//****** Object: Table [recall].[tblApplicant_Callback] ScriptDate: 12/09/2003 12:15:19 ******/if exists (select * from dbo.sysobjects where id =object_id(N'[recall].[tblApplicant_Callback]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [recall].[tblApplicant_Callback]GO/****** Object: Table [recall].[tblApplicant_Callback] ScriptDate: 12/09/2003 12:15:22 ******/CREATE TABLE [recall].[tblApplicant_Callback] ([fldCallbackID] [int] IDENTITY (1, 1) NOT NULL ,[fldCampaignID] [int] NULL ,[fldApplicantID] [int] NULL ,[fldApplicantCampaignID] [int] NULL ,[fldScriptID] [int] NULL ,[fldCallBack] [datetime] NULL ,[fldTitle] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldSurname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fldFirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldPostCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldAddress1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldAddress2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldTown] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldCounty] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fldTelephoneNo_1] [varchar] (20) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[fldTelephoneNo_2] [varchar] (20) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[fldTelephoneNo_3] [varchar] (20) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[fldNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldReason] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fldAttempts] [smallint] NULL ,[fldInvited] [tinyint] NULL ,[fldResult] [tinyint] NULL ,[fldEventSession] [int] NULL ,[fldUnabletoAttend] [bit] NOT NULL ,[fldEntered] [datetime] NULL ,[fldEnteredBy] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldLock] [bit] NOT NULL ,[upsize_ts] [timestamp] NULL ,[fldCallPeriod1] [tinyint] NOT NULL ,[fldCallPeriod1_From] [datetime] NULL ,[fldCallPeriod1_To] [datetime] NULL ,[fldCallPeriod2] [tinyint] NOT NULL ,[fldCallPeriod2_From] [datetime] NULL ,[fldCallPeriod2_To] [datetime] NULL ,[fldLastedCalled] [datetime] NULL ,[fldDeadlineDate] [datetime] NULL ,[fldFax] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldMobile] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldEmail] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldHouseNo] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fldLockDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [recall].[tblApplicant_Callback] WITH NOCHECK ADDCONSTRAINT [aaaaatblApplicant_Callback_PK] PRIMARY KEY CLUSTERED([fldCallbackID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [recall].[tblApplicant_Callback] WITH NOCHECK ADDCONSTRAINT [DF__Temporary__fldAt__3FD07829] DEFAULT (0) FOR[fldAttempts],CONSTRAINT [DF_tblApplicant_Callback_fldUnabletoAttend] DEFAULT (0)FOR [fldUnabletoAttend],CONSTRAINT [DF_tblApplicant_Callback_fldLock] DEFAULT (0) FOR[fldLock],CONSTRAINT [DF_tblApplicant_Callback_fldCallPeriod1] DEFAULT (1) FOR[fldCallPeriod1],CONSTRAINT [DF_tblApplicant_Callback_fldCallPeriod2] DEFAULT (1) FOR[fldCallPeriod2]GOCREATE INDEX [fldFirstName] ON[recall].[tblApplicant_Callback]([fldFirstName]) WITH FILLFACTOR = 90ON [PRIMARY]GOCREATE INDEX [fldSurname] ON[recall].[tblApplicant_Callback]([fldSurname]) WITH FILLFACTOR = 90ON [PRIMARY]GOCREATE INDEX [fldCallPeriod1_From] ON[recall].[tblApplicant_Callback]([fldCallPeriod1_From]) WITHFILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [fldCallPeriod1_To] ON[recall].[tblApplicant_Callback]([fldCallPeriod1_To]) WITH FILLFACTOR= 90 ON [PRIMARY]GOCREATE INDEX [fldCallPeriod2_From] ON[recall].[tblApplicant_Callback]([fldCallPeriod2_From]) WITHFILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [fldCallPeriod2_To] ON[recall].[tblApplicant_Callback]([fldCallPeriod2_To]) WITH FILLFACTOR= 90 ON [PRIMARY]GOCREATE INDEX [fldCallPeriod1] ON[recall].[tblApplicant_Callback]([fldCallPeriod1]) ON [PRIMARY]GOCREATE INDEX [fldCallPeriod2] ON[recall].[tblApplicant_Callback]([fldCallPeriod2]) ON [PRIMARY]GO/* Stored Procedure 1 */CREATE PROCEDURE recall_Update_ResumeCallback_Changes@CallbackID int,@CampIDint,@CallBackDatedatetime,@Titlevarchar(4),@FirstNamevarchar(50),@Surnamevarchar(50),@Postcodevarchar(10),@HomeTelvarchar(20),@Mobilevarchar(20),@WorkTelvarchar(20),@Notestext,@CallPeriod1tinyint,@CallPeriod1_Fromdatetime,@CallPeriod1_Todatetime,@CallPeriod2tinyint,@CallPeriod2_Fromdatetime,@CallPeriod2_Todatetime,@LastCalledDatedatetime,@Attemptssmallint,@HouseNovarchar(25)ASBEGINUPDATE recall.tblApplicant_Callback with (rowlock)SETfldCampaignID=@CampID,fldCallBack=@CallBackDate,fldTitle=@Title,fldFirstName=@FirstName,fldSurname=@Surname,fldPostcode=@Postcode,fldTelephoneNo_1=@HomeTel,fldTelephoneNo_2=@Mobile,fldTelephoneNo_3=@WorkTel,fldNotes=@Notes,fldCallPeriod1=@CallPeriod1,fldCallPeriod1_From=@CallPeriod1_From,fldCallPeriod1_To=@CallPeriod1_To,fldCallPeriod2=@CallPeriod2,fldCallPeriod2_From=@CallPeriod2_From,fldCallPeriod2_To=@CallPeriod2_To,fldLastedCalled=@LastCalledDate,fldAttempts=@Attempts,fldHouseNo=@HouseNoWHERE (fldCallbackID= @CallbackID)ENDGO/* Stored Procedure 2 */CREATE PROCEDURE recall_Update_Campaign_Telescreening_Resume_Lock@CallbackID int,@Lock bitASBEGINUPDATE recall.tblApplicant_Callback with (rowlock)SET fldLock = @LockWHERE (fldCallbackID = @CallbackID)ENDGO
When I try to debug the break points will always say the source code is different from the current version, but the custom component in the GAC has the new version number. The other strange thing is the toolbox will not reset to the original version meaning it will not remove the custom components. The funny thing is after I compile the custom components and restart VS the custom component runs with the new code changes. I can see the new features I added, but the debugger and toolbox still seem to be broken.
I have tried the following 1) Reset the tool box. 2) uninstall all my custom dll from the GAC €œC:WINDOWSassembly€? 3) remove all my custom dll from €œC:Program FilesMicrosoft SQL Server90DTSPipelineComponents€? 4) restart VS 2005 5) reselect the custom components. 6) reboot my computer.
It seem like VS has another cache. For the tool box or something.
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.
Table 1 - dbo.DimHC_Team - This stores TeamID and TeamName Table 2 - dbo.DimHC_Team_Config - This has TeamID TeamDate (not really used anywhere) TeamHeadCount (Max strength of a team)
Table 3 - dbo.DimHC_Team_Agg - This has TeamID, Team_Date (1st of every month, so that when I see the data for past months I use these dates) , Permanent Employees, Contracted employees, Open positions, Total, HeadCount.
Now, I have a stored procedure which goes like this -
if (not exists (select * from dbo.DimHC_Team_Agg where Team_Date = @datevar2)) begin
insert into dbo.DimHC_Team_Agg (TeamID, Team_Date) select TeamID , @datevar2 from dbo.DimHC_Team_Config end
update dbo.DimHC_Team_Agg set Team_Head_Count = dbo.DimHC_Team_Config.Team_Head_Count from dbo.DimHC_Team_Config, dbo.DimHC_Team_Agg where dbo.DimHC_Team_Agg.Team_Date = @datevar2 and dbo.DimHC_Team_Config.TeamID = dbo.DimHC_Team_Agg.TeamID
update dbo.DimHC_Team_Agg set Perm_Posn = (select count(EmpType) from dbo.uview_DimHC_Emp_view1 where EmpStartDate < = @datevar4 and EmpType = 'Permanent' and (EmpEndDate = '01/01/1753' or EmpEndDate > @datevar3) and TeamID = '1'), Contract_Posn = (select count(EmpType) from dbo.uview_DimHC_Emp_view1 where EmpStartDate < = @datevar4 and EmpType = 'Contractor' and (EmpEndDate = '01/01/1753' or EmpEndDate > @datevar3) and TeamID = '1') ......
What this block is doing is converting today's date to the first of this month. Going one month and 2 months in advance. Then it checks if any employee's start date < 10th of the next month and end date > 31st of the next month and if both the conditions are met then increments the employee count for that team as 1.
This TeamID is then repeated for 13 teams that I have right now. So that every month I will run it and it will update.
Now, I have 2 problems - First being that I had to write this block for 13 teams which isn't really a smart thing to do. Let's say I add a team tomorrow then I will need to modify this stored procedure and then tomorrow I may not be here, so the person who uses it should be able to do it easily. So, I wanted to know if I can loop or something through this with the max limit of TeamID coming from some external source and not like fix it to 20 or something.
Second, let's say in the beginning of this month I add a new team. The way I will do it is I will add it to dbo.DimHC_Team and dbo.DimHC_Team_Config table. Now, if I run this stored procedure then
if (not exists (select * from dbo.DimHC_Team_Agg where Team_Date = @datevar2))
is not going to return NULL as there will be entries with some teams right now. So this new Team won't be inserted in the dbo.DimHC_Team_Agg table and I will have to manually do it.
Can somebody help me as to how can I get over with these two problems?
Sorry for being really long but I thought it best to give all the details.
I'm using SQL 7, there is a setting on DB properties called "Auto update statistics", what kind of statistics does this refers to and how can this stats be accessed?
