I'm creating a performance testing server. I'll describe my approach,
maybe the group gurus can review it and suggest improvements. Please
tell me if this is a sound approach.
I want to have global performance measurements. They should guide us
in improving the database overall responsiveness. So, if we add a
complex clustered index that speed a query, but slows a frequent
update, it should appear in the measurements.
Here are the steps I followed:
1) Backup of the production database.
2) Recording of a representative SQL Profiler trace file of the
production database
using the TSQL_Replay template. It will be used as my base
workload.
3) Restoring the backup to a Microsoft Virtual Server instance
(http://www.microsoft.com/windowsserversystem/virtualserver/ )
4) Waste a lot of time configuring login permissions and making the
database have the same
Id number of the original one so I can replay the trace file.
5) Turning on the "Enable Undo disks" option in the Virtual Hard Disk
Properties of the Virtual
server instance.
6) Replay the saved trace file, saving another trace file to gather
performance data.
7) Load the new trace file to a table and run some queries to measure
the database
performance.
8) Discard all the modifications of the virtual server instances,
Tweak the database and go to
step 6 to gather new data.
Is it a sound approach? Are there better ways to do it?
Now is my doubt: which measurements should I take from the saved
profile?
There are a lot of possible events to record in the Profiler trace,
and in each one the fields mean something different.
Here is my initial list of queries on the trace table:
This is probably my most important measure. It gives me the total
duration and CPU spent:
SELECT sum(Duration) as total_time , sum(CPU) total_cpu,
sum(Duration - CPU) as total_wait FROM trace WHERE EventClass = 12 -- 12 is SQL:BatchCompleted
If instead of SQL:BatchCompleted, I use SQL:StmtCompleted I get a
smaller value for sum(duration). If I choose to sum the Duration
column of the SQLTransaction EventClass (number 50) with the commit
EventSubClass (1) the sum(duration) is bigger. Which one gives me the
better measurement?
Here is the commited transactions total, average, max and standard
deviation duration:
SELECT sum(Duration) as trans_total_time , AVG(Duration) as
average_time, max(Duration) as max_transaction_time, stdev(Duration)
as standard_deviation
FROM trace WHERE EventClass = 50 and EventSubClass =1
This is the time wasted rolling back transactions:
SELECT sum(Duration) FROM trace
WHERE EventClass = 50 and EventSubClass =2 -- duration of roolback
transactions
Time spent waiting for locks:
SELECT sum(Duration) FROM trace
WHERE EventClass = 24 -- 24 is Lock:Acquired event
Number of deadlocks:
SELECT MAX(IntegerData) FROM trace WHERE EventClass=59 --
Lock:Deadlock Chain
How do I measure the time wasted when a deadlock happens and a query
is canceled? Is it the duration of the Lock:Cancel event class?
Do the gurus here have any idea of more good data to collect?
BTW, I'm using SQL Server 2000
kind regards,
Paulo Eduardo Neves
http://NaoTemMosquito.blogspot.comHi Paulo
You will need to start your replay trace before yuo take the backup to make
sure it contains all changes between the backup finishing and the trace
starting. You can remove the backup commands after as well as other steps you
don't want. You will need to match database ids with the new system (or
change the trace). If you create the corresponding logins on the new server
you should be able to match the SIDs using sp_change_users_login, so mapping
users should not be a great deal!
For analysis you can use Bill Graziano's cleartrace at
www.cleardata.biz/cleartrace/download
If you are tuning then you should look at statements, frequency as well as
duration and I/O may also be important.
John
"Paulo Eduardo Neves" wrote:
> I'm creating a performance testing server. I'll describe my approach,
> maybe the group gurus can review it and suggest improvements. Please
> tell me if this is a sound approach.
> I want to have global performance measurements. They should guide us
> in improving the database overall responsiveness. So, if we add a
> complex clustered index that speed a query, but slows a frequent
> update, it should appear in the measurements.
> Here are the steps I followed:
> 1) Backup of the production database.
> 2) Recording of a representative SQL Profiler trace file of the
> production database
> using the TSQL_Replay template. It will be used as my base
> workload.
> 3) Restoring the backup to a Microsoft Virtual Server instance
> (http://www.microsoft.com/windowsserversystem/virtualserver/ )
> 4) Waste a lot of time configuring login permissions and making the
> database have the same
> Id number of the original one so I can replay the trace file.
> 5) Turning on the "Enable Undo disks" option in the Virtual Hard Disk
> Properties of the Virtual
> server instance.
> 6) Replay the saved trace file, saving another trace file to gather
> performance data.
> 7) Load the new trace file to a table and run some queries to measure
> the database
> performance.
> 8) Discard all the modifications of the virtual server instances,
> Tweak the database and go to
> step 6 to gather new data.
> Is it a sound approach? Are there better ways to do it?
> Now is my doubt: which measurements should I take from the saved
> profile?
> There are a lot of possible events to record in the Profiler trace,
> and in each one the fields mean something different.
> Here is my initial list of queries on the trace table:
> This is probably my most important measure. It gives me the total
> duration and CPU spent:
> SELECT sum(Duration) as total_time , sum(CPU) total_cpu,
> sum(Duration - CPU) as total_wait FROM trace WHERE EventClass => 12 -- 12 is SQL:BatchCompleted
> If instead of SQL:BatchCompleted, I use SQL:StmtCompleted I get a
> smaller value for sum(duration). If I choose to sum the Duration
> column of the SQLTransaction EventClass (number 50) with the commit
> EventSubClass (1) the sum(duration) is bigger. Which one gives me the
> better measurement?
> Here is the commited transactions total, average, max and standard
> deviation duration:
> SELECT sum(Duration) as trans_total_time , AVG(Duration) as
> average_time, max(Duration) as max_transaction_time, stdev(Duration)
> as standard_deviation
> FROM trace WHERE EventClass = 50 and EventSubClass =1
> This is the time wasted rolling back transactions:
> SELECT sum(Duration) FROM trace
> WHERE EventClass = 50 and EventSubClass =2 -- duration of roolback
> transactions
> Time spent waiting for locks:
> SELECT sum(Duration) FROM trace
> WHERE EventClass = 24 -- 24 is Lock:Acquired event
> Number of deadlocks:
> SELECT MAX(IntegerData) FROM trace WHERE EventClass=59 --
> Lock:Deadlock Chain
> How do I measure the time wasted when a deadlock happens and a query
> is canceled? Is it the duration of the Lock:Cancel event class?
> Do the gurus here have any idea of more good data to collect?
> BTW, I'm using SQL Server 2000
> kind regards,
> Paulo Eduardo Neves
> http://NaoTemMosquito.blogspot.com
>|||On Wed, 03 Oct 2007 12:22:03 -0700, Paulo Eduardo Neves
<pauloneves@.gmail.com> wrote:
>Do the gurus here have any idea of more good data to collect?
Looks like you've got the idea, but the basic structure I always
follow does a group by on the command text. Now, this can be tricky,
I think SQL 2005 has added some features to help there ... but you're
on 2K, so never mind that!
Anyway, my preferred figure of merit is reads, not duration. They
tend to correspond heavily, but reads is logical reads and is somewhat
independent of load, and tells you what it is costing to run that
command.
So,
select
top 20
substring(textdata,1,30) as cmd,
sum(reads) as sumreads,
avg(reads) as avgreads,
count(*) as cnt
from dbo.yourtractable
where eventclass in (rpc done, batch done)
group by substring(textdata,1,30)
order by 2
Now, if you're tuning an entire system, you need to create a total
picture, you need to come up with a way to baseline your performance
and measure the quantitative components and improvements you make. I
guess clocktime through a trace is a good overall measure.
... but this also assumes a lot of stuff about your IO system not
being saturated, and such. When the hardware starts to run out, these
softer statistics become hard to interpret. And replaying it on a VM,
boy, I dunno about that. But in any case, probably the most robust
and significant figure is logical reads, in my ever so humble (or not
so humble) opinion.
Josh|||On Oct 4, 6:17 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> You will need to start your replay trace before yuo take the backup to make
> sure it contains all changes between the backup finishing and the trace
> starting. You can remove the backup commands after as well as other steps you
> don't want.
I did a backup just a little before. I don't mind if some queries fail
later during the replay. They fail and the replay continues. They
would be insignificant when I calculate the total time.
You will need to match database ids with the new system (or
> change the trace). If you create the corresponding logins on the new server
> you should be able to match the SIDs using sp_change_users_login, so mapping
> users should not be a great deal!
And the default database of the replay users should be the one you are
testing.
> For analysis you can use Bill Graziano's cleartrace atwww.cleardata.biz/cleartrace/download
I've already seen Cleartrace. It looks very useful. The problem is
that I currently just have access to a SQL Server 2000 installation
and it doesn't run in the free version of SQL Server 2005. It looks
like that cleartrace doesn't give me global measurements, just
aggregate the statements.
Thanks for your tips.
regards,
Paulo Eduardo Neves
http://NaoTemMosquito.blogspot.com|||On Oct 5, 2:33 am, JXStern <JXSternChange...@.gte.net> wrote:
> On Wed, 03 Oct 2007 12:22:03 -0700, Paulo Eduardo Neves
> <paulone...@.gmail.com> wrote:
> >Do the gurus here have any idea of more good data to collect?
>
...
> Anyway, my preferred figure of merit is reads, not duration. They
> tend to correspond heavily, but reads is logical reads and is somewhat
> independent of load, and tells you what it is costing to run that
> command.
Yes. But I believe that the problem in my hands are caused due to
excessive locking in the database. Measuring Reads and Writes won't
display the locking problems.
> So,
> select
> top 20
> substring(textdata,1,30) as cmd,
> sum(reads) as sumreads,
> avg(reads) as avgreads,
> count(*) as cnt
> from dbo.yourtractable
> where eventclass in (rpc done, batch done)
> group by substring(textdata,1,30)
> order by 2
I'll try to use the cleartrace tool to get the statement aggregate
data.
> Now, if you're tuning an entire system, you need to create a total
> picture, you need to come up with a way to baseline your performance
> and measure the quantitative components and improvements you make. I
> guess clocktime through a trace is a good overall measure.
Will the clocktime value be different from the total transactions
duration?
> ... but this also assumes a lot of stuff about your IO system not
> being saturated, and such. When the hardware starts to run out, these
> softer statistics become hard to interpret. And replaying it on a VM,
> boy, I dunno about that. But in any case, probably the most robust
> and significant figure is logical reads, in my ever so humble (or not
> so humble) opinion.
First I'm working in a proof of concept server. After showing its
utility, I'll lobby for a dedicated performance testing machine. If I
guarantee that there's nothing else running in the machine, and put
the replay trace file and the performance recording trace in other
harddisk than the one with the database file, I believe that I will
have consistent performance measurements.
I've had a nice idea about the setup. Filtering the trace events where
Duration != 0, will record just the interesting log events.
Thanks for the good ideas!
Paulo
http://NaoTemMosquito.blogspot.com|||Hi Paulo
Replaying traces will potentially highlight problems and a very useful
facility, it is not guaranteed that they always find your problems. Check out
books online to see the restictions when replaying a trace. You may want to
consider using a test tool such as Microsoft Team Suite, LoadRunner or
Rational Performance Tester etc..
If you have locking problems look at usings some of the locking events to
check what your system is doing, rather than relying on time/reads/writes etc.
John
"Paulo Eduardo Neves" wrote:
> On Oct 5, 2:33 am, JXStern <JXSternChange...@.gte.net> wrote:
> > On Wed, 03 Oct 2007 12:22:03 -0700, Paulo Eduardo Neves
> >
> > <paulone...@.gmail.com> wrote:
> > >Do the gurus here have any idea of more good data to collect?
> >
> ...
> > Anyway, my preferred figure of merit is reads, not duration. They
> > tend to correspond heavily, but reads is logical reads and is somewhat
> > independent of load, and tells you what it is costing to run that
> > command.
> Yes. But I believe that the problem in my hands are caused due to
> excessive locking in the database. Measuring Reads and Writes won't
> display the locking problems.
> > So,
> >
> > select
> > top 20
> > substring(textdata,1,30) as cmd,
> > sum(reads) as sumreads,
> > avg(reads) as avgreads,
> > count(*) as cnt
> > from dbo.yourtractable
> > where eventclass in (rpc done, batch done)
> > group by substring(textdata,1,30)
> > order by 2
> I'll try to use the cleartrace tool to get the statement aggregate
> data.
> >
> > Now, if you're tuning an entire system, you need to create a total
> > picture, you need to come up with a way to baseline your performance
> > and measure the quantitative components and improvements you make. I
> > guess clocktime through a trace is a good overall measure.
> Will the clocktime value be different from the total transactions
> duration?
> >
> > ... but this also assumes a lot of stuff about your IO system not
> > being saturated, and such. When the hardware starts to run out, these
> > softer statistics become hard to interpret. And replaying it on a VM,
> > boy, I dunno about that. But in any case, probably the most robust
> > and significant figure is logical reads, in my ever so humble (or not
> > so humble) opinion.
> First I'm working in a proof of concept server. After showing its
> utility, I'll lobby for a dedicated performance testing machine. If I
> guarantee that there's nothing else running in the machine, and put
> the replay trace file and the performance recording trace in other
> harddisk than the one with the database file, I believe that I will
> have consistent performance measurements.
> I've had a nice idea about the setup. Filtering the trace events where
> Duration != 0, will record just the interesting log events.
> Thanks for the good ideas!
> Paulo
> http://NaoTemMosquito.blogspot.com
>
>
Showing posts with label profiler. Show all posts
Showing posts with label profiler. Show all posts
Monday, March 19, 2012
Saturday, February 25, 2012
Checkpoint causes need for better IO subsystem?
Using Profiler and PerfMon, when there is a checkpoint, the durations of
INSERTS and SELECTS increase to approx 5000ms ... up from 15-30M...which
causes distress for clients and needs to be fixed.
Note that not ALL of the INSERTS and SELECTS are afffected..perhaps during
the 10 seconds that the checkpoint takes place..10% have a duration increase.
It's during pereids of batch inserts that this happens which occurs many
times during the day at odd intervals.. I've written about this before and
someone suggested that the batch inserts take place off-peak. Can't be done.
The nature of the business dictates otherwise.
It's also been suggested that a better IO subsystem be installed. We're
using a 168bit/sec controller card and using PerfMon and tracking data
transfered over all of the hard drives, that during these batch inserts, the
total IO bits/sec is not even half of the 168bit/sec capacity, i.e., the
controller is able to handle the data.
To answer your other question..The MDF, LDF and C: drive are all on their
own physical separate disk drives and have been defragmented. These are huge
130GB drives. There is 4GB of Ram on each server. Dual CPUs at 2396MHZ.
Any Help appreciated.
Don
SQL 2000 SP4
Checkpoints tend to be semi-random writes across the entire database file
footprint. Batch inserts can be sequential or not, depending on whether
your clustered index based on a monotonically increasing column. As such,
the random write capability of the drives comes into play, not the data
throughput limit. Besides, the theoretical limits stated by the
manufacturers are under very narrowly defined conditions. If you believe
manufacturer specs match up to SQL Server usage, I have a bridge I would
like to offer for sale.
Given that you are on a bus architecture disk subsystem, high write activity
can block read activity, this causing your slow response. Five to ten
seconds typically matches the duration of a normal checkpoint. A high-end
disk subsystem with one or more gigabytes of cache and a full-duplex
connection path can help. That translates to a Fibre-Channel connected SAN.
I would also check on the Page Life Expectency performance counter. If it
is low, you may benefit from more physical RAM in the server. This will
allow more data to stay in cache longer, thus eliminating the need to
constantly reload the data from the disks.
Finally, you can change the clustered indexes to use a monotonically
increasing key, thus making the data loads sequential and reducing the
number of page splits, random IO operations, and overall server load during
a data load.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:614A712D-3C95-44F7-9D6A-719788FAAC83@.microsoft.com...
> Using Profiler and PerfMon, when there is a checkpoint, the durations of
> INSERTS and SELECTS increase to approx 5000ms ... up from 15-30M...which
> causes distress for clients and needs to be fixed.
> Note that not ALL of the INSERTS and SELECTS are afffected..perhaps during
> the 10 seconds that the checkpoint takes place..10% have a duration
> increase.
> It's during pereids of batch inserts that this happens which occurs many
> times during the day at odd intervals.. I've written about this before and
> someone suggested that the batch inserts take place off-peak. Can't be
> done.
> The nature of the business dictates otherwise.
> It's also been suggested that a better IO subsystem be installed. We're
> using a 168bit/sec controller card and using PerfMon and tracking data
> transfered over all of the hard drives, that during these batch inserts,
> the
> total IO bits/sec is not even half of the 168bit/sec capacity, i.e., the
> controller is able to handle the data.
> To answer your other question..The MDF, LDF and C: drive are all on their
> own physical separate disk drives and have been defragmented. These are
> huge
> 130GB drives. There is 4GB of Ram on each server. Dual CPUs at 2396MHZ.
> Any Help appreciated.
> Don
> SQL 2000 SP4
>
>
|||The Page Life Expectency performance counter hoovers around 850..not sure if
that's good or bad.
There's still 700M of RAM available and SQL's set dynamically to use all 4GB
of RAM if needed. I'm thinking if SQL needed more RAM, it's there for the
taking.
Not sure about how to setup a monotonically increasing key.
Currently, the Clustered index is on multiple cols (2)... (Name, Date)
Would a monotonically increasing key include a new column with an
incrementing sequential value?
such as this?
(newvalue, Name, Date)
don
"Geoff N. Hiten" wrote:
> Checkpoints tend to be semi-random writes across the entire database file
> footprint. Batch inserts can be sequential or not, depending on whether
> your clustered index based on a monotonically increasing column. As such,
> the random write capability of the drives comes into play, not the data
> throughput limit. Besides, the theoretical limits stated by the
> manufacturers are under very narrowly defined conditions. If you believe
> manufacturer specs match up to SQL Server usage, I have a bridge I would
> like to offer for sale.
> Given that you are on a bus architecture disk subsystem, high write activity
> can block read activity, this causing your slow response. Five to ten
> seconds typically matches the duration of a normal checkpoint. A high-end
> disk subsystem with one or more gigabytes of cache and a full-duplex
> connection path can help. That translates to a Fibre-Channel connected SAN.
> I would also check on the Page Life Expectency performance counter. If it
> is low, you may benefit from more physical RAM in the server. This will
> allow more data to stay in cache longer, thus eliminating the need to
> constantly reload the data from the disks.
> Finally, you can change the clustered indexes to use a monotonically
> increasing key, thus making the data loads sequential and reducing the
> number of page splits, random IO operations, and overall server load during
> a data load.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:614A712D-3C95-44F7-9D6A-719788FAAC83@.microsoft.com...
>
>
|||850 is a bit on the low side. 4000-10000 or higher is considered good. As
it is, you are rewriting memory every 14 minutes. Not great.
Identity columns provide monotonically increasing keys. SQL creates a
clustered index out of your primary key by default, but that is not a
requirement. You can separate the two.
Narrow clustered indexes work better. Google the following string for some
excellent articles on clustered index selection and its impact on
performance:
clustered index sql kimberly tripp
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:3F381A8B-84FB-4131-917E-24416EA5FE7E@.microsoft.com...[vbcol=seagreen]
> The Page Life Expectency performance counter hoovers around 850..not sure
> if
> that's good or bad.
> There's still 700M of RAM available and SQL's set dynamically to use all
> 4GB
> of RAM if needed. I'm thinking if SQL needed more RAM, it's there for the
> taking.
> Not sure about how to setup a monotonically increasing key.
> Currently, the Clustered index is on multiple cols (2)... (Name, Date)
> Would a monotonically increasing key include a new column with an
> incrementing sequential value?
> such as this?
> (newvalue, Name, Date)
> don
> "Geoff N. Hiten" wrote:
|||donsql22222 (donsql22222@.discussions.microsoft.com) writes:
> There's still 700M of RAM available and SQL's set dynamically to use all
> 4GB of RAM if needed. I'm thinking if SQL needed more RAM, it's there
> for the taking.
Just a check: you have Enterprise Edition? Standard only handles 2GB of
memory.
> Not sure about how to setup a monotonically increasing key.
> Currently, the Clustered index is on multiple cols (2)... (Name, Date)
> Would a monotonically increasing key include a new column with an
> incrementing sequential value?
> such as this?
Name does not sound like it would grow monotonically. :-) Furthermore it
sounds like something I would avoid in a clustred index. Since the
clustered key is also the row-locator in a non-clustered index, a wide
clustered index also make the NC indexes wide and less effecient.
What about the date, is always today's date, or could it be far in
the past? Dates are often good for monotonically clustered indexes.
Of course, there may be other parts of the application that would
perform less well, if there is no clustered index on name.
One alternative is to create the clustered index with a low fill
factor, say 50%. That would create gaps that newly inserted data
can be filled into, and you would thus avoid page splits. This
strategy would require you to routinely rebuild the index, to create
new gaps. I learned this idea from SQL Server MVP Greg Linwood. He
used GUIDs for this, and they are truely random. Nmaes may be less
random and the strategy may work less well for names.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||My strategy (stolen from Kimberly) is to create a clustered key from an
identity column. Lest I provoke the Wrath of Celko(tm), I don't actually
use that column anywhere in the application. It is simply to (a) force
insert order at the end of the table, and (b) provide for a very narrow
clustered key for index lookups and index intersection. It is a physical
characteristic only and has no place in my logical data model. Thus, the
Primary Key is materialized by a non-clustered index.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9780EDE041297Yazorman@.127.0.0.1...
> donsql22222 (donsql22222@.discussions.microsoft.com) writes:
> Just a check: you have Enterprise Edition? Standard only handles 2GB of
> memory.
>
> Name does not sound like it would grow monotonically. :-) Furthermore it
> sounds like something I would avoid in a clustred index. Since the
> clustered key is also the row-locator in a non-clustered index, a wide
> clustered index also make the NC indexes wide and less effecient.
> What about the date, is always today's date, or could it be far in
> the past? Dates are often good for monotonically clustered indexes.
> Of course, there may be other parts of the application that would
> perform less well, if there is no clustered index on name.
> One alternative is to create the clustered index with a low fill
> factor, say 50%. That would create gaps that newly inserted data
> can be filled into, and you would thus avoid page splits. This
> strategy would require you to routinely rebuild the index, to create
> new gaps. I learned this idea from SQL Server MVP Greg Linwood. He
> used GUIDs for this, and they are truely random. Nmaes may be less
> random and the strategy may work less well for names.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||The Kimberly webcast of indexing was great. Thanks.
Now, is this a monotonically increasing indexing scheme that I've created?
Dropped all indexes.
I added a new field of type Indentity, decimal.
I then created a clustered unique index on this field.
I then created a nonclustered index on Name, date.
I'm still showing the problem indicated earlier...during checkpoints, some
large increases in duration of some INSERTS and SELECTS.
If this monotonically increasing that I've created looks correct, I might
just leave it in as it sounds like it has some performance benefits.
Thanks,
Don
"Geoff N. Hiten" wrote:
> My strategy (stolen from Kimberly) is to create a clustered key from an
> identity column. Lest I provoke the Wrath of Celko(tm), I don't actually
> use that column anywhere in the application. It is simply to (a) force
> insert order at the end of the table, and (b) provide for a very narrow
> clustered key for index lookups and index intersection. It is a physical
> characteristic only and has no place in my logical data model. Thus, the
> Primary Key is materialized by a non-clustered index.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9780EDE041297Yazorman@.127.0.0.1...
>
>
|||Kimberly is an excellent speaker. She is consistantly one of the top if not
the top rated speaker at any conference where she presents.
I usually use int or bigint for identity columns but decimal should be OK.
I like int and bigint for index intersection tuning. The new index
structure should help with caching and table fragmentation. You still may
have an inadequate IO subsystem, but at least your load isn't artifically
increased by a bad indexing scheme. I have had problems with checkpoints
slowing down regular IO before on SCSI disk arrays. RAID level choice will
have a drastic affect on how rapidly the subsystem can absorb data. See if
you can estimate the size of the checkpoint using performance monitor. If
it is over 300 MB or so, you probably will have to go to a SAN to completely
remove the performance hit.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:7008FA05-F0A5-4736-9C4D-C95FA19F3BA2@.microsoft.com...[vbcol=seagreen]
> The Kimberly webcast of indexing was great. Thanks.
> Now, is this a monotonically increasing indexing scheme that I've created?
> Dropped all indexes.
> I added a new field of type Indentity, decimal.
> I then created a clustered unique index on this field.
> I then created a nonclustered index on Name, date.
> I'm still showing the problem indicated earlier...during checkpoints, some
> large increases in duration of some INSERTS and SELECTS.
> If this monotonically increasing that I've created looks correct, I might
> just leave it in as it sounds like it has some performance benefits.
> Thanks,
> Don
>
> "Geoff N. Hiten" wrote:
|||Kimberly really is an outstanding presenter! I can't say enough positive
things about her indexing webcast. I'm a believer. I listened to it again,
and will again this afternoon as there's things I pickup each time through.
I'm feeling optimistic. I've put monotonically increasing indexes with
bigint on the identify col on all the tables in the DB..even the small ones
that were just heaps. I've got the LDF and MDF on their own defragged
physical drives. And preliminary tests show that now the highest duration is
approx 300ms during the checkpoint where it was 4000-5000ms for "some" of
the INSERTS before this. So i'm hoping!
There's only 9M records in the testDB so I'll not sure if the behavior will
change with the production size of approx 1.5B records in each of 3 tables.
Will be testing it in the next few days.
btw, the size of the checkpoint is only around 150MG...that led me to think
maybe it's not the IO and that maybe it's an indexing performance issue.
Don
"Geoff N. Hiten" wrote:
> Kimberly is an excellent speaker. She is consistantly one of the top if not
> the top rated speaker at any conference where she presents.
> I usually use int or bigint for identity columns but decimal should be OK.
> I like int and bigint for index intersection tuning. The new index
> structure should help with caching and table fragmentation. You still may
> have an inadequate IO subsystem, but at least your load isn't artifically
> increased by a bad indexing scheme. I have had problems with checkpoints
> slowing down regular IO before on SCSI disk arrays. RAID level choice will
> have a drastic affect on how rapidly the subsystem can absorb data. See if
> you can estimate the size of the checkpoint using performance monitor. If
> it is over 300 MB or so, you probably will have to go to a SAN to completely
> remove the performance hit.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:7008FA05-F0A5-4736-9C4D-C95FA19F3BA2@.microsoft.com...
>
>
INSERTS and SELECTS increase to approx 5000ms ... up from 15-30M...which
causes distress for clients and needs to be fixed.
Note that not ALL of the INSERTS and SELECTS are afffected..perhaps during
the 10 seconds that the checkpoint takes place..10% have a duration increase.
It's during pereids of batch inserts that this happens which occurs many
times during the day at odd intervals.. I've written about this before and
someone suggested that the batch inserts take place off-peak. Can't be done.
The nature of the business dictates otherwise.
It's also been suggested that a better IO subsystem be installed. We're
using a 168bit/sec controller card and using PerfMon and tracking data
transfered over all of the hard drives, that during these batch inserts, the
total IO bits/sec is not even half of the 168bit/sec capacity, i.e., the
controller is able to handle the data.
To answer your other question..The MDF, LDF and C: drive are all on their
own physical separate disk drives and have been defragmented. These are huge
130GB drives. There is 4GB of Ram on each server. Dual CPUs at 2396MHZ.
Any Help appreciated.
Don
SQL 2000 SP4
Checkpoints tend to be semi-random writes across the entire database file
footprint. Batch inserts can be sequential or not, depending on whether
your clustered index based on a monotonically increasing column. As such,
the random write capability of the drives comes into play, not the data
throughput limit. Besides, the theoretical limits stated by the
manufacturers are under very narrowly defined conditions. If you believe
manufacturer specs match up to SQL Server usage, I have a bridge I would
like to offer for sale.
Given that you are on a bus architecture disk subsystem, high write activity
can block read activity, this causing your slow response. Five to ten
seconds typically matches the duration of a normal checkpoint. A high-end
disk subsystem with one or more gigabytes of cache and a full-duplex
connection path can help. That translates to a Fibre-Channel connected SAN.
I would also check on the Page Life Expectency performance counter. If it
is low, you may benefit from more physical RAM in the server. This will
allow more data to stay in cache longer, thus eliminating the need to
constantly reload the data from the disks.
Finally, you can change the clustered indexes to use a monotonically
increasing key, thus making the data loads sequential and reducing the
number of page splits, random IO operations, and overall server load during
a data load.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:614A712D-3C95-44F7-9D6A-719788FAAC83@.microsoft.com...
> Using Profiler and PerfMon, when there is a checkpoint, the durations of
> INSERTS and SELECTS increase to approx 5000ms ... up from 15-30M...which
> causes distress for clients and needs to be fixed.
> Note that not ALL of the INSERTS and SELECTS are afffected..perhaps during
> the 10 seconds that the checkpoint takes place..10% have a duration
> increase.
> It's during pereids of batch inserts that this happens which occurs many
> times during the day at odd intervals.. I've written about this before and
> someone suggested that the batch inserts take place off-peak. Can't be
> done.
> The nature of the business dictates otherwise.
> It's also been suggested that a better IO subsystem be installed. We're
> using a 168bit/sec controller card and using PerfMon and tracking data
> transfered over all of the hard drives, that during these batch inserts,
> the
> total IO bits/sec is not even half of the 168bit/sec capacity, i.e., the
> controller is able to handle the data.
> To answer your other question..The MDF, LDF and C: drive are all on their
> own physical separate disk drives and have been defragmented. These are
> huge
> 130GB drives. There is 4GB of Ram on each server. Dual CPUs at 2396MHZ.
> Any Help appreciated.
> Don
> SQL 2000 SP4
>
>
|||The Page Life Expectency performance counter hoovers around 850..not sure if
that's good or bad.
There's still 700M of RAM available and SQL's set dynamically to use all 4GB
of RAM if needed. I'm thinking if SQL needed more RAM, it's there for the
taking.
Not sure about how to setup a monotonically increasing key.
Currently, the Clustered index is on multiple cols (2)... (Name, Date)
Would a monotonically increasing key include a new column with an
incrementing sequential value?
such as this?
(newvalue, Name, Date)
don
"Geoff N. Hiten" wrote:
> Checkpoints tend to be semi-random writes across the entire database file
> footprint. Batch inserts can be sequential or not, depending on whether
> your clustered index based on a monotonically increasing column. As such,
> the random write capability of the drives comes into play, not the data
> throughput limit. Besides, the theoretical limits stated by the
> manufacturers are under very narrowly defined conditions. If you believe
> manufacturer specs match up to SQL Server usage, I have a bridge I would
> like to offer for sale.
> Given that you are on a bus architecture disk subsystem, high write activity
> can block read activity, this causing your slow response. Five to ten
> seconds typically matches the duration of a normal checkpoint. A high-end
> disk subsystem with one or more gigabytes of cache and a full-duplex
> connection path can help. That translates to a Fibre-Channel connected SAN.
> I would also check on the Page Life Expectency performance counter. If it
> is low, you may benefit from more physical RAM in the server. This will
> allow more data to stay in cache longer, thus eliminating the need to
> constantly reload the data from the disks.
> Finally, you can change the clustered indexes to use a monotonically
> increasing key, thus making the data loads sequential and reducing the
> number of page splits, random IO operations, and overall server load during
> a data load.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:614A712D-3C95-44F7-9D6A-719788FAAC83@.microsoft.com...
>
>
|||850 is a bit on the low side. 4000-10000 or higher is considered good. As
it is, you are rewriting memory every 14 minutes. Not great.
Identity columns provide monotonically increasing keys. SQL creates a
clustered index out of your primary key by default, but that is not a
requirement. You can separate the two.
Narrow clustered indexes work better. Google the following string for some
excellent articles on clustered index selection and its impact on
performance:
clustered index sql kimberly tripp
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:3F381A8B-84FB-4131-917E-24416EA5FE7E@.microsoft.com...[vbcol=seagreen]
> The Page Life Expectency performance counter hoovers around 850..not sure
> if
> that's good or bad.
> There's still 700M of RAM available and SQL's set dynamically to use all
> 4GB
> of RAM if needed. I'm thinking if SQL needed more RAM, it's there for the
> taking.
> Not sure about how to setup a monotonically increasing key.
> Currently, the Clustered index is on multiple cols (2)... (Name, Date)
> Would a monotonically increasing key include a new column with an
> incrementing sequential value?
> such as this?
> (newvalue, Name, Date)
> don
> "Geoff N. Hiten" wrote:
|||donsql22222 (donsql22222@.discussions.microsoft.com) writes:
> There's still 700M of RAM available and SQL's set dynamically to use all
> 4GB of RAM if needed. I'm thinking if SQL needed more RAM, it's there
> for the taking.
Just a check: you have Enterprise Edition? Standard only handles 2GB of
memory.
> Not sure about how to setup a monotonically increasing key.
> Currently, the Clustered index is on multiple cols (2)... (Name, Date)
> Would a monotonically increasing key include a new column with an
> incrementing sequential value?
> such as this?
Name does not sound like it would grow monotonically. :-) Furthermore it
sounds like something I would avoid in a clustred index. Since the
clustered key is also the row-locator in a non-clustered index, a wide
clustered index also make the NC indexes wide and less effecient.
What about the date, is always today's date, or could it be far in
the past? Dates are often good for monotonically clustered indexes.
Of course, there may be other parts of the application that would
perform less well, if there is no clustered index on name.
One alternative is to create the clustered index with a low fill
factor, say 50%. That would create gaps that newly inserted data
can be filled into, and you would thus avoid page splits. This
strategy would require you to routinely rebuild the index, to create
new gaps. I learned this idea from SQL Server MVP Greg Linwood. He
used GUIDs for this, and they are truely random. Nmaes may be less
random and the strategy may work less well for names.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||My strategy (stolen from Kimberly) is to create a clustered key from an
identity column. Lest I provoke the Wrath of Celko(tm), I don't actually
use that column anywhere in the application. It is simply to (a) force
insert order at the end of the table, and (b) provide for a very narrow
clustered key for index lookups and index intersection. It is a physical
characteristic only and has no place in my logical data model. Thus, the
Primary Key is materialized by a non-clustered index.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9780EDE041297Yazorman@.127.0.0.1...
> donsql22222 (donsql22222@.discussions.microsoft.com) writes:
> Just a check: you have Enterprise Edition? Standard only handles 2GB of
> memory.
>
> Name does not sound like it would grow monotonically. :-) Furthermore it
> sounds like something I would avoid in a clustred index. Since the
> clustered key is also the row-locator in a non-clustered index, a wide
> clustered index also make the NC indexes wide and less effecient.
> What about the date, is always today's date, or could it be far in
> the past? Dates are often good for monotonically clustered indexes.
> Of course, there may be other parts of the application that would
> perform less well, if there is no clustered index on name.
> One alternative is to create the clustered index with a low fill
> factor, say 50%. That would create gaps that newly inserted data
> can be filled into, and you would thus avoid page splits. This
> strategy would require you to routinely rebuild the index, to create
> new gaps. I learned this idea from SQL Server MVP Greg Linwood. He
> used GUIDs for this, and they are truely random. Nmaes may be less
> random and the strategy may work less well for names.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||The Kimberly webcast of indexing was great. Thanks.
Now, is this a monotonically increasing indexing scheme that I've created?
Dropped all indexes.
I added a new field of type Indentity, decimal.
I then created a clustered unique index on this field.
I then created a nonclustered index on Name, date.
I'm still showing the problem indicated earlier...during checkpoints, some
large increases in duration of some INSERTS and SELECTS.
If this monotonically increasing that I've created looks correct, I might
just leave it in as it sounds like it has some performance benefits.
Thanks,
Don
"Geoff N. Hiten" wrote:
> My strategy (stolen from Kimberly) is to create a clustered key from an
> identity column. Lest I provoke the Wrath of Celko(tm), I don't actually
> use that column anywhere in the application. It is simply to (a) force
> insert order at the end of the table, and (b) provide for a very narrow
> clustered key for index lookups and index intersection. It is a physical
> characteristic only and has no place in my logical data model. Thus, the
> Primary Key is materialized by a non-clustered index.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9780EDE041297Yazorman@.127.0.0.1...
>
>
|||Kimberly is an excellent speaker. She is consistantly one of the top if not
the top rated speaker at any conference where she presents.
I usually use int or bigint for identity columns but decimal should be OK.
I like int and bigint for index intersection tuning. The new index
structure should help with caching and table fragmentation. You still may
have an inadequate IO subsystem, but at least your load isn't artifically
increased by a bad indexing scheme. I have had problems with checkpoints
slowing down regular IO before on SCSI disk arrays. RAID level choice will
have a drastic affect on how rapidly the subsystem can absorb data. See if
you can estimate the size of the checkpoint using performance monitor. If
it is over 300 MB or so, you probably will have to go to a SAN to completely
remove the performance hit.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:7008FA05-F0A5-4736-9C4D-C95FA19F3BA2@.microsoft.com...[vbcol=seagreen]
> The Kimberly webcast of indexing was great. Thanks.
> Now, is this a monotonically increasing indexing scheme that I've created?
> Dropped all indexes.
> I added a new field of type Indentity, decimal.
> I then created a clustered unique index on this field.
> I then created a nonclustered index on Name, date.
> I'm still showing the problem indicated earlier...during checkpoints, some
> large increases in duration of some INSERTS and SELECTS.
> If this monotonically increasing that I've created looks correct, I might
> just leave it in as it sounds like it has some performance benefits.
> Thanks,
> Don
>
> "Geoff N. Hiten" wrote:
|||Kimberly really is an outstanding presenter! I can't say enough positive
things about her indexing webcast. I'm a believer. I listened to it again,
and will again this afternoon as there's things I pickup each time through.
I'm feeling optimistic. I've put monotonically increasing indexes with
bigint on the identify col on all the tables in the DB..even the small ones
that were just heaps. I've got the LDF and MDF on their own defragged
physical drives. And preliminary tests show that now the highest duration is
approx 300ms during the checkpoint where it was 4000-5000ms for "some" of
the INSERTS before this. So i'm hoping!
There's only 9M records in the testDB so I'll not sure if the behavior will
change with the production size of approx 1.5B records in each of 3 tables.
Will be testing it in the next few days.
btw, the size of the checkpoint is only around 150MG...that led me to think
maybe it's not the IO and that maybe it's an indexing performance issue.
Don
"Geoff N. Hiten" wrote:
> Kimberly is an excellent speaker. She is consistantly one of the top if not
> the top rated speaker at any conference where she presents.
> I usually use int or bigint for identity columns but decimal should be OK.
> I like int and bigint for index intersection tuning. The new index
> structure should help with caching and table fragmentation. You still may
> have an inadequate IO subsystem, but at least your load isn't artifically
> increased by a bad indexing scheme. I have had problems with checkpoints
> slowing down regular IO before on SCSI disk arrays. RAID level choice will
> have a drastic affect on how rapidly the subsystem can absorb data. See if
> you can estimate the size of the checkpoint using performance monitor. If
> it is over 300 MB or so, you probably will have to go to a SAN to completely
> remove the performance hit.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:7008FA05-F0A5-4736-9C4D-C95FA19F3BA2@.microsoft.com...
>
>
Thursday, February 16, 2012
checking bandwidth of network
Hi ,
how can i check if the current bandwidth that i am having is enough for
the operation ? can i use the performance monitor or the profiler(which item
to choose) ?
tks & rdgs
what operation do you have in mind? performance monitor can show you network
bandwidth consumption. Even the task manager can do it.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
> Hi ,
> how can i check if the current bandwidth that i am having is enough for
> the operation ? can i use the performance monitor or the profiler(which
> item
> to choose) ?
> tks & rdgs
|||Hi ,
i am thinking of monitoring the SQL SERVER status from a client PC and the
operations i meant earlier are just some normal updates , select queries thru
a client interface
tks & rdgs
"wei xiao" wrote:
> what operation do you have in mind? performance monitor can show you network
> bandwidth consumption. Even the task manager can do it.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
>
>
how can i check if the current bandwidth that i am having is enough for
the operation ? can i use the performance monitor or the profiler(which item
to choose) ?
tks & rdgs
what operation do you have in mind? performance monitor can show you network
bandwidth consumption. Even the task manager can do it.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
> Hi ,
> how can i check if the current bandwidth that i am having is enough for
> the operation ? can i use the performance monitor or the profiler(which
> item
> to choose) ?
> tks & rdgs
|||Hi ,
i am thinking of monitoring the SQL SERVER status from a client PC and the
operations i meant earlier are just some normal updates , select queries thru
a client interface
tks & rdgs
"wei xiao" wrote:
> what operation do you have in mind? performance monitor can show you network
> bandwidth consumption. Even the task manager can do it.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
>
>
Tuesday, February 14, 2012
checking bandwidth of network
Hi ,
how can i check if the current bandwidth that i am having is enough for
the operation ? can i use the performance monitor or the profiler(which item
to choose) ?
tks & rdgswhat operation do you have in mind? performance monitor can show you network
bandwidth consumption. Even the task manager can do it.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
> Hi ,
> how can i check if the current bandwidth that i am having is enough for
> the operation ? can i use the performance monitor or the profiler(which
> item
> to choose) ?
> tks & rdgs|||Hi ,
i am thinking of monitoring the SQL SERVER status from a client PC and the
operations i meant earlier are just some normal updates , select queries thru
a client interface
tks & rdgs
"wei xiao" wrote:
> what operation do you have in mind? performance monitor can show you network
> bandwidth consumption. Even the task manager can do it.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
> > Hi ,
> >
> > how can i check if the current bandwidth that i am having is enough for
> > the operation ? can i use the performance monitor or the profiler(which
> > item
> > to choose) ?
> >
> > tks & rdgs
>
>
how can i check if the current bandwidth that i am having is enough for
the operation ? can i use the performance monitor or the profiler(which item
to choose) ?
tks & rdgswhat operation do you have in mind? performance monitor can show you network
bandwidth consumption. Even the task manager can do it.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
> Hi ,
> how can i check if the current bandwidth that i am having is enough for
> the operation ? can i use the performance monitor or the profiler(which
> item
> to choose) ?
> tks & rdgs|||Hi ,
i am thinking of monitoring the SQL SERVER status from a client PC and the
operations i meant earlier are just some normal updates , select queries thru
a client interface
tks & rdgs
"wei xiao" wrote:
> what operation do you have in mind? performance monitor can show you network
> bandwidth consumption. Even the task manager can do it.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
> > Hi ,
> >
> > how can i check if the current bandwidth that i am having is enough for
> > the operation ? can i use the performance monitor or the profiler(which
> > item
> > to choose) ?
> >
> > tks & rdgs
>
>
checking bandwidth of network
Hi ,
how can i check if the current bandwidth that i am having is enough for
the operation ? can i use the performance monitor or the profiler(which item
to choose) ?
tks & rdgswhat operation do you have in mind? performance monitor can show you network
bandwidth consumption. Even the task manager can do it.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
> Hi ,
> how can i check if the current bandwidth that i am having is enough for
> the operation ? can i use the performance monitor or the profiler(which
> item
> to choose) ?
> tks & rdgs|||Hi ,
i am thinking of monitoring the SQL SERVER status from a client PC and the
operations i meant earlier are just some normal updates , select queries thr
u
a client interface
tks & rdgs
"wei xiao" wrote:
> what operation do you have in mind? performance monitor can show you netwo
rk
> bandwidth consumption. Even the task manager can do it.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
>
>
how can i check if the current bandwidth that i am having is enough for
the operation ? can i use the performance monitor or the profiler(which item
to choose) ?
tks & rdgswhat operation do you have in mind? performance monitor can show you network
bandwidth consumption. Even the task manager can do it.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
> Hi ,
> how can i check if the current bandwidth that i am having is enough for
> the operation ? can i use the performance monitor or the profiler(which
> item
> to choose) ?
> tks & rdgs|||Hi ,
i am thinking of monitoring the SQL SERVER status from a client PC and the
operations i meant earlier are just some normal updates , select queries thr
u
a client interface
tks & rdgs
"wei xiao" wrote:
> what operation do you have in mind? performance monitor can show you netwo
rk
> bandwidth consumption. Even the task manager can do it.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:FCEFEFEB-F6E0-4FB3-AB78-80C8E3DC6076@.microsoft.com...
>
>
Friday, February 10, 2012
Check Stored Procedure execution duration
Hello,
I need to check the duration of execution of some Stored
Procedures. I dont know when these stored procedures will
run, so i execute the Profiler to catch these Stored
Procedures but i dont know if im going to get the desired
result.
Am I doing it right or may i do anything else?
Best regards.
You are on the right way, Profiler is exactly the tool you need.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>
|||Hi,
"You are doing right."
Use Profiler - Use the template name " SQLProfilerTSQL_duration" in
profiler. In the filter you can give the procedure names you
have to trace for duration.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>
I need to check the duration of execution of some Stored
Procedures. I dont know when these stored procedures will
run, so i execute the Profiler to catch these Stored
Procedures but i dont know if im going to get the desired
result.
Am I doing it right or may i do anything else?
Best regards.
You are on the right way, Profiler is exactly the tool you need.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>
|||Hi,
"You are doing right."
Use Profiler - Use the template name " SQLProfilerTSQL_duration" in
profiler. In the filter you can give the procedure names you
have to trace for duration.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>
Check Stored Procedure execution duration
Hello,
I need to check the duration of execution of some Stored
Procedures. I dont know when these stored procedures will
run, so i execute the Profiler to catch these Stored
Procedures but i dont know if im going to get the desired
result.
Am I doing it right or may i do anything else?
Best regards.You are on the right way, Profiler is exactly the tool you need.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx
.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>|||Hi,
"You are doing right."
Use Profiler - Use the template name " SQLProfilerTSQL_duration" in
profiler. In the filter you can give the procedure names you
have to trace for duration.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx
.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>
I need to check the duration of execution of some Stored
Procedures. I dont know when these stored procedures will
run, so i execute the Profiler to catch these Stored
Procedures but i dont know if im going to get the desired
result.
Am I doing it right or may i do anything else?
Best regards.You are on the right way, Profiler is exactly the tool you need.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx
.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>|||Hi,
"You are doing right."
Use Profiler - Use the template name " SQLProfilerTSQL_duration" in
profiler. In the filter you can give the procedure names you
have to trace for duration.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx
.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>
Subscribe to:
Posts (Atom)