FTS Index Size: time

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

2012年3月29日星期四

Fluctuate in DB Performance Statistics

Dear All,
I have noticed that the DB performance statistics is not
stable as it will rise at certain time dramatically.
I have checked that there isn't any job at that time.
E.g. Server\memory/pages , Current disk queue length,
processor time
Can any one suggest on any cause this?
Thanks.most database applications today consists of a mix of
simple transactions and complex queries that process a lot
of data. this includes reports or other sophisticated
features.
if you have for example 100 concurrent users running the
simple transactions, you might see a stable load on the
server,
however, anytime someone hits one of the complex queries,
you might see cpu spike.
this is why many people recommend separating OLTP and DSS
applications. there is no such thing as a machine powerful
enough to handle both functions on one server.
the OLTP server depends on fast response times, meaning it
should operate at low cpu loading.
the DSS app is supposed to run with cpu pegged, if not,
then you should be running more DSS queries to it, you
bought the hardware, you may as well use it.
>--Original Message--
>Dear All,
>I have noticed that the DB performance statistics is not
>stable as it will rise at certain time dramatically.
>I have checked that there isn't any job at that time.
>E.g. Server\memory/pages , Current disk queue length,
>processor time
>Can any one suggest on any cause this?
>Thanks.
>.
>|||Run profiler at that time to see if it is any TSQL commands causing this. Also, it can be the
checkpoint process.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jackty" <anonymous@.discussions.microsoft.com> wrote in message
news:022d01c3ad86$140683a0$a601280a@.phx.gbl...
> Dear All,
> I have noticed that the DB performance statistics is not
> stable as it will rise at certain time dramatically.
> I have checked that there isn't any job at that time.
> E.g. Server\memory/pages , Current disk queue length,
> processor time
> Can any one suggest on any cause this?
> Thanks.

Flow Control in SSIS

I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.

When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

Thanks for the reply. I was using contraints and what you descibe is how I put it in my original message. However, after a good night's sleep I see that in my package I the drop table task comes after the table is renamed, rather than before. It appears SSIS was trying its best to complete all those items the best it could.

After putting all in correct order, the package ran as it should.

Flow Control in SSIS

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

After putting all in correct order, the package ran as it should.

sql

float vs decimal - computation time

I have heard that using floats in calculated fields are much faster than
using decimals. Is this true? What is the role of the floating-point
processor in these computations?
JasonCP Developer (steved@.newsgroup.nospam) writes:
> I have heard that using floats in calculated fields are much faster than
> using decimals. Is this true? What is the role of the floating-point
> processor in these computations?
Instead of asking again, why not researching the responses to your post
from Wednesday?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland et al,
I apologize. I meant to follow-up my earlier post and I realize that it was
both unclear and unnecessary to repost. I have found what I was looking for.
Thank you very much for your help.
CP Developer
"Erland Sommarskog" wrote:

> CP Developer (steved@.newsgroup.nospam) writes:
> Instead of asking again, why not researching the responses to your post
> from Wednesday?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

2012年3月9日星期五

Fix dependencies during or after renaming

The previous post regarding dependencies reminded me of another bugger I come
up with from time to time. SQL Server allows us to rename objects via a
system stored procedure (I usually use an ADP front-end that presumably calls
the same procedure), but after the rename, I find singnificant strangeness.

For instance, triggers are still connected to the tables, but the trigger text
still refers to the old table name. Views and stored procedures dependent on
the table are broken, and are not fixed by even by restarting the server,
though I thought the cached plans were supposed to be dumped on shut-down,
then recreated on demand later.

Is there some tool or technique to rename a table and automatically detect and
properly drop/rebuild dependent triggers, views, stored procedures,
relationships, and anything else I'm not thinking of?

Oh yeah, and there are extended properties to deal with as well, but I guess
that's intractible unless we know what client build them (e.g. Access ADP),
and what it expects to be recorded there, but I suppose it would be nice to
just dump them rather than leave them there possibly corrupted from a client's
persective.Steve Jorgensen wrote:

> The previous post regarding dependencies reminded me of another bugger I come
> up with from time to time. SQL Server allows us to rename objects via a
> system stored procedure (I usually use an ADP front-end that presumably calls
> the same procedure), but after the rename, I find singnificant strangeness.
> For instance, triggers are still connected to the tables, but the trigger text
> still refers to the old table name. Views and stored procedures dependent on
> the table are broken, and are not fixed by even by restarting the server,
> though I thought the cached plans were supposed to be dumped on shut-down,
> then recreated on demand later.
> Is there some tool or technique to rename a table and automatically detect and
> properly drop/rebuild dependent triggers, views, stored procedures,
> relationships, and anything else I'm not thinking of?
> Oh yeah, and there are extended properties to deal with as well, but I guess
> that's intractible unless we know what client build them (e.g. Access ADP),
> and what it expects to be recorded there, but I suppose it would be nice to
> just dump them rather than leave them there possibly corrupted from a client's
> persective.

One thing I know about views, if you rename a view, you best double
click it in EM and rename it in the create statement as well otherwise
it will get renamed back to the old name if ever you script it.
(checks his 4 views, nope, that's not why that happened).|||On Fri, 23 Jan 2004 11:01:27 GMT, Steve Jorgensen <nospam@.nospam.nospam>
wrote:

Oh yeah, I guess the views and procedures don't get the table names updated in
the code, so that one's obvious (though I'd still like an automated way to fix
it). What -was- confusing about that was why renaming a -column- causes
problems for stored procedures and views, even after a restart, even if the
procedure or view is uing an asterisk in the select and not directly
referencing the column in question.|||Steve Jorgensen (nospam@.nospam.nospam) writes:
> Oh yeah, I guess the views and procedures don't get the table names
> updated in the code, so that one's obvious (though I'd still like an
> automated way to fix it). What -was- confusing about that was why
> renaming a -column- causes problems for stored procedures and views,
> even after a restart, even if the procedure or view is uing an asterisk
> in the select and not directly referencing the column in question.

Hm, I wrote this procedure:

create proc test_sp as select * from test

Which I subsequently executed. And I found that test had two columns
'mystring' and 'col'.

Then I ran:

sp_rename 'test.mystring', hisstring, 'column'

Without restarting the server, I successfully ran the procedure, and
the column was called 'hisstring'. I then created:

create view test_view as select * from test

And then:

sp_rename 'test.hisstring', herstring, 'column'

I was able to select from the view after this. However in the view
the column was still named 'hisstring'. This is because SQL Server
stores the name of the columns in the view in the system tables. This
can be remedied with:

exec sp_refreshview test_view

Finally, I would like to point out that I, and many with me, find it
to be bad coding practice to use SELECT * in production code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Steve Jorgensen (nospam@.nospam.nospam) writes:
> The previous post regarding dependencies reminded me of another bugger I
> come up with from time to time. SQL Server allows us to rename objects
> via a system stored procedure (I usually use an ADP front-end that
> presumably calls the same procedure), but after the rename, I find
> singnificant strangeness.
> For instance, triggers are still connected to the tables, but the
> trigger text still refers to the old table name. Views and stored
> procedures dependent on the table are broken, and are not fixed by even
> by restarting the server, though I thought the cached plans were
> supposed to be dumped on shut-down, then recreated on demand later.
> Is there some tool or technique to rename a table and automatically
> detect and properly drop/rebuild dependent triggers, views, stored
> procedures, relationships, and anything else I'm not thinking of?

The major enterprise data-modelling tools offers such features.

If you have your code under version-control - and this is what you should -
you could run some search/replace tool, provided that the old column
name is significant enough.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

FiscalWeekOfQuarter missing

When I build a time dimension, FiscalWeekOfQuarter does not show up. On the following MSDN page it is listed as an attribute.

http://msdn2.microsoft.com/en-US/library/ms175662.aspx#time_dimension_attribute_types

Anyone know how I can add it? Thanks.

Can you describe the steps you took to build the dimension? I just ran through the dimension wizard, building a server-based time dimension and an attribute of type "FiscalWeekOfQuarter" was created by the wizard. I also looked at an existing time dimension I had based on a dimension table and I can select "FiscalWeekOfQuarter" as the Type for an existing dimension attribute...

Dave Fackler

FiscalWeekOfQuarter missing

When I build a time dimension, FiscalWeekOfQuarter does not show up. On the following MSDN page it is listed as an attribute.

http://msdn2.microsoft.com/en-US/library/ms175662.aspx#time_dimension_attribute_types

Anyone know how I can add it? Thanks.

Dave Fackler

2012年3月7日星期三

First Timer Having an Insert Problem

Ok...I am stumped

I am currently writing a custom DTS package using an ActiveX script. This is my first time writing an ActiveX script and using the VBscripting language.

What I need to do is take data from three existing MS SQL Server 2k tables and import it into a single new table after serveral numerical data manipulations.

The specific problem I am having is that when I insert my recorsets into the new table several of the records are out of their sequential order. I am confused because if I cut down on the amount of data I insert, either in the number of rows and/or number of columns, I have no problem with my insert, but when I insert all the data I need things get out of order and swap places. I am inserting using what I think is called a connection/execute command with TSQL commands, and from what I have read this is the most efficient way to go about it.

I have approximately 4800 rows to insert with 6 columns, but my program seems to error with I try to insert in excess of 4100 rows.

Does anyone have any ideas? I was told that it might have something to do with a buffer, but I have not been able to find any helpful documentation. I have included the code for my insert loop.

Thank you in Advance!

-TRoche

do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value

GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value

GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value

'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "

DestCon.Execute DestCmd

GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext

LoopDo you have a primary key on GPSIMPORT? If not, then your data structure is known as a "heap" and SQL server makes no guarantees about the order in which data is stored, or even the order in which it is retrieved in consecutive statements.

It is not a good idea to rely on the order in which data is inserted to be the order in which it is kept or retrieved. Define a primary key for your data.

blindman|||Thanks a lot Blindman!!

This seems to have worked!! I would have never found that solution.

thanks again,

TRoche|||"First timer having an insert problem."?

Maybe that's why you always remember your first....

First time, replication

Why does the replication process adds a rowguid, a
uniqueidentifier, column in most of my tables? Is this a
default/necessary behaviour? It is causing some conflicts,
so could this be suppress? TIA
That's a normal behavior for merge replication.
Merge replication requires a universal unique way to identify each row.
So that's why it adds a rowguid to every table involved in a publication
unless this table already possesses a column of such type
"John" <anonymous@.discussions.microsoft.com> a crit dans le message de
news: 10ea01c50fc4$61538f80$a401280a@.phx.gbl...
> Why does the replication process adds a rowguid, a
> uniqueidentifier, column in most of my tables? Is this a
> default/necessary behaviour? It is causing some conflicts,
> so could this be suppress? TIA

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,