FTS Index Size: stored

显示标签为“stored”的博文。显示所有博文

2012年3月29日星期四

Flow Control in stored procedures

A newbie question..

If within a stored procedure there are a list of Stored Procedure calls.

Create Procedure spTest AS

EXEC spMyActionQuery1
EXEC spMyActionQuery2
EXEC spMyActionQuery3
EXEC spMyActionQuery4

GO

Assuming each sub stored procedure executes an action query, does SQL Server ALWAYS wait for the current procedure to finish execution before proceding with the next stored procedure? I believe the answer is yes.The answer IS yes :D

2012年3月19日星期一

FK

I know that in the case of a primary key (non-clustered)
the index part of the foreign key must be stored
separately from the table. Is this true if you have a
foreign key on a table? Is there data stored externally
to the table for a foreign key?
Also: uou don't have to defrag or reindex a foreign key
do you?A foreign key is just a rule. SQL Server doesn't have to store anything
additional as you add the FK constraint. SQL Server will, however, make sure
that your data adheres to your rules, so a check is performed as you modify
data. Creating an index on the foreign key column can be very beneficial to
assist for this validation. Not to mention that you often perform joins over
primary key - foreign key relationships. And remember that a FK column can
be a good candidate for the clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"GXX" <anonymous@.discussions.microsoft.com> wrote in message
news:442201c4020f$c3ca8150$a601280a@.phx.gbl...
> I know that in the case of a primary key (non-clustered)
> the index part of the foreign key must be stored
> separately from the table. Is this true if you have a
> foreign key on a table? Is there data stored externally
> to the table for a foreign key?
> Also: uou don't have to defrag or reindex a foreign key
> do you?

2012年3月7日星期三

First time, it times-out. After that it is fine.

I have a stored procedure that runs slowly and takes over 60 seconds sometimes, but almost all of the time it takes only one or two seconds. In both cases the same input data is used and there have been no changes to the table. The slow running is after the query has not been run for several days.

In the background 16 records are inserted every minute. The table has 5 million records. There are two indexes on the table one is clustered the other is unclustered. They are very well tuned. The record contains a two integers and 90 floats.

The stored procedure gets one record from the database which is the latest record for a particular entity. If I go to the web site after a few days the page times out. If I go to Mgmt Studio, and run the sp form there it takes a very long time but eventually comes back with the correct answer. If execute the stored procedure after that the response is a second or two using the same input data. If I change the input data the response is equally as fast. The web site also works very quickly too.

What is going on and how do I fix it?

Pops73

Try running the SP with SET STATISTICS IO ON, then look at the messages tab. It sounds like the first time you run the query, it is having to do physical reads as opposed to logical reads. The second time you run it, it is doing logical reads, which would be much, much faster.|||

Thank you for the response and it sounds reasonable. I restarted the server which I thought would clear the buffer cache and cause the problem to occur. But it didn't after the restart the timeout did not occur. Response was good. Is the buffer cache saved through a restart of a server? How can I clear the cache to easily reproduce the problem and gather the statistics?

Pops73

|||

Restarting the server does clear the the buffer cache. You can run the command DBCC FREEPROCCACHE to clear out the procedure cache, and DBCC DROPCLEANBUFFERS to flush the data cache.

http://msdn2.microsoft.com/en-US/library/ms187762.aspx

Run these commands:

DBCC FREEPROCCACHE

CHECKPOINT

DBCC DROPCLEANBUFFERS

|||

Thank you for your help. This still does not reproduce the problem.

Table '#7F0FE051'. Scan count 0, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TempMtrScan'. Scan count 1, logical reads 8, physical reads 3, read-ahead reads 607, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

More info -- I moved the indexes. I have the data in one file group with three files and the indexes in another file group with two files. I still saw the problem.

running Sql Server 2005 sp1

I had the problem yesterday in another db instance of the same applicaiton on another machine. This table only had one record instead of 5 million. Two different queries will show the same problem

Part of the table definition

CREATE TABLE [dbo].[TempMtrScan](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[ShovelID] [int] NOT NULL,

[DateTime] [datetime] NOT NULL,

[FHoistNDE] [float] NULL,

[FHoistDE] [float] NULL,

[FHoistIP] [float] NULL,

[FHoistFLD] [float] NULL, ............

Query 1

INSERT INTO @.TempMtrScanUnpivot ([ShovelID],[TempMtrScanName], [Temperature])

SELECT ShovelID, TempMtrScanName, Temperature

FROM

(SELECT ShovelID,[DateTime],

FHoistNDE,FHoistDE,

RHoistNDE,RHoistDE,

CrowdNDE,CrowdDE,

SwingNDE,SwingDE,

RearSwingNDE,RearSwingDE,

RPropelNDE,RPropelDE,

LPropelNDE,LPropelDE,

LFrontSwingNDE,LFrontSwingDE,

RFrontSwingNDE,RFrontSwingDE,

TransPhaseA,TransPhaseB,TransPhaseC,

DriveInletTemp1,DriveInletTemp2,DriveInletTemp3,DriveInletTemp4,

DriveInletTemp5,DriveInletTemp6,DriveInletTemp7,DriveInletTemp8,

CwdMotorTemp,CwdMotorWindABTemp,CwdMotorWindBCTemp,CwdMotorWindCATemp,

PplRhtMtrMotorTemp,PplRhtMtrMotorWindABTemp,PplRhtMtrMotorWindBCTemp,PplRhtMtrMotorWindCATemp,

SwgLFntMtrMotorTemp,SwgLFntMtrMotorWindABTemp,SwgLFntMtrMotorWindBCTemp,SwgLFntMtrMotorWindCATemp,

HstRearMtrMotorTemp,HstRearMtrMotorWindABTemp,HstRearMtrMotorWindBCTemp,HstRearMtrMotorWindCATemp,

PplLftMtrMotorTemp,PplLftMtrMotorWindABTemp,PplLftMtrMotorWindBCTemp,PplLftMtrMotorWindCATemp,

SwgRFntMtrMotorTemp,SwgRFntMtrMotorWindABTemp,SwgRFntMtrMotorWindBCTemp,SwgRFntMtrMotorWindCATemp,

HstFntMtrMotorTemp,HstFntMtrMotorWindABTemp,HstFntMtrMotorWindBCTemp,HstFntMtrMotorWindCATemp,

SwgRearMtrMotorTemp,SwgRearMtrMotorWindABTemp,SwgRearMtrMotorWindBCTemp,SwgRearMtrMotorWindCATemp,

SwgFntMtrMotorTemp, SwgFntMtrMotorWindABTemp, SwgFntMtrMotorWindBCTemp, SwgFntMtrMotorWindCATemp, ACS800Temp,

TempDiffPhsU, TempDiffPhsV, TempDiffPhsW

FROM dbo.TempMtrScan

WHERE [Datetime] =

(SELECT MAX([datetime])

FROM dbo.TempMtrScan

WHERE ShovelID = @.ShovelID)

AND ShovelID = @.ShovelID) A

UNPIVOT (

Temperature

FOR TempMtrScanName

IN ( FHoistNDE,FHoistDE,