Auto Update Stats Causing Blocking
Mar 10, 2003
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.
spid kpid blocked waittype waittime lastwaittype waitresource
------ ------ ------- -------- ----------- -------------------------------- -----------------------------
140 4292 142 0x0005 68609 LCK_M_X TAB: 7:1659921035 [[COMPILE]]
137 2576 140 0x0005 64671 LCK_M_X TAB: 7:1659921035 [[COMPILE]]
View 1 Replies
ADVERTISEMENT
Mar 10, 2003
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.
View 2 Replies
View Related
Oct 20, 2007
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
View 3 Replies
View Related
Aug 12, 2015
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?
View 2 Replies
View Related
Apr 10, 2008
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
View 1 Replies
View Related
Apr 10, 2008
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
View 3 Replies
View Related
Dec 29, 2007
The auto stats not working
I have both Auto Update Statistics and Auto Update Statistics Asynchronously set to True
Created a little test table.
USE [TEST]
GO
/****** Object: Table [dbo].[CUSTOMER] Script Date: 12/29/2007 10:42:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CUSTOMER](
[Customer_Id] [nchar](10) NOT NULL,
[Customer_Name] [nvarchar](1000) NULL,
[Customer_Address] [nvarchar](1000) NULL,
[Customer_Address1] [nchar](1000) NULL,
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED
(
[Customer_Id] ASC
)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
I then 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 does not have 2000 rows in here.
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 ?
View 7 Replies
View Related
Dec 29, 2007
The auto stats not working
I have both Auto Update Statistics and Auto Update Statistics Asynchronously set to True
USE [TEST]
GO
/****** Object: Table [dbo].[CUSTOMER] Script Date: 12/29/2007 10:42:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CUSTOMER](
[Customer_Id] [nchar](10) NOT NULL,
[Customer_Name] [nvarchar](1000) NULL,
[Customer_Address] [nvarchar](1000) NULL,
[Customer_Address1] [nchar](1000) NULL,
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED
(
[Customer_Id] ASC
)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 ?
View 2 Replies
View Related
Feb 11, 2015
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.
The CROSS JOIN and CROSS APPLY seem suspect.
(
@p0 DATETIME,
@p1 INT,
@p2 INT,
@p3 NVARCHAR(4000),
@p4 INT,
[code]....
View 9 Replies
View Related
May 31, 2007
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.
View 11 Replies
View Related
Aug 11, 2004
Is it true that Update Statistics perform record commits if a query is outstanding?
View 4 Replies
View Related
Aug 8, 2007
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?
View 6 Replies
View Related
Apr 17, 2006
SQL Server 2000 on Win2k
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.
Any other advice would be appreciated.
Cheers.
View 2 Replies
View Related
Mar 12, 2015
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
DECLARE @commandNVARCHAR(MAX)
CREATE TABLE #worktable
(
[Database]SYSNAME
,SchemaNameSYSNAME
,ObjectNameSYSNAME
,StatsNameSYSNAME
,ColNameSYSNAME
[code]....
View 6 Replies
View Related
Nov 6, 2007
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?
Thanks !
View 11 Replies
View Related
Sep 26, 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
View 1 Replies
View Related
Jul 22, 2004
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?
Clive
View 1 Replies
View Related
Feb 17, 2012
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.
View 3 Replies
View Related
Jul 22, 2014
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.
View 5 Replies
View Related
Mar 24, 2015
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.
View 3 Replies
View Related
Sep 28, 2015
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.
View 10 Replies
View Related
Apr 9, 2014
I noticed today a session that was executing a FULL SCAN update as follows:
UPDATE STATISTICS [XXXX].[XXXX].[XXXX] [_WA_Sys_00000009_318D45CA] WITH FULLSCAN
When I checked the sys.dm_exec_query_memory_grants DMV for the session I could see the following values:
requested_memory_kb granted_memory_kb used_memory_kb max_used_memory_kb
145,705,216 145,705,216 139,977,336 139,980,408
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?
View 1 Replies
View Related
Mar 18, 2015
I have a job that runs nightly, rebuild index. job runs fine every night but every few weeks it fails.
USE msdb
GO
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
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.
View 9 Replies
View Related
Jun 26, 2015
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?
View 2 Replies
View Related
Jun 15, 2015
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.
View 5 Replies
View Related
Jan 14, 2005
Hello all,
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.
Any suggestions?
Thank you,
Aaron B
View 1 Replies
View Related
Feb 20, 2008
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?
Thanks in advance.
View 3 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
May 29, 2006
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.
Does anybody have any suggestion?
View 10 Replies
View Related
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.
View 4 Replies
View Related
Jan 25, 2006
I discovered the cause:
in SQL Server Enterprise Manager, open up db tree, right click the table, go - 'design' and set 'Identity' to yes.
View 1 Replies
View Related
Jun 16, 2008
Hi,
I am back with some more questions.
The structure is something like this -
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 -
declare @datevar datetime
declare @datevar2 datetime
declare @datevar3 datetime
declare @datevar4 datetime
select @datevar = DATEADD(dd, -(Day(getdate()) -1) ,getdate())
select @datevar = dateadd(dd, datediff(d,0,@datevar),0)
select @datevar2 = Dateadd(m , 1 , @datevar)
select @datevar4 = Dateadd(dd , 9 , @datevar2)
select @datevar3 = dateadd(m,2,@datevar)
select @datevar3 = dateadd(dd, -2 , @datevar3)
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.
View 20 Replies
View Related
Apr 5, 2006
Hi all,
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?
Thanks for your help!
Gustavo
Thanks,
GS
View 1 Replies
View Related
