hi everone
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMER
The only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>
|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our queries
> on that table
> Thanks in advance
> Erdem KEMER
>
|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing the
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. If
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especially
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduce
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Friday, March 30, 2012
ROWLOCK
hi everone
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMERThe only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our queries
> on that table
> Thanks in advance
> Erdem KEMER
>|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing the
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. If
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especially
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduce
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMERThe only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our queries
> on that table
> Thanks in advance
> Erdem KEMER
>|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing the
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. If
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especially
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduce
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.
Wednesday, March 28, 2012
ROWLOCK
hi everone
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMERThe only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our querie
s
> on that table
> Thanks in advance
> Erdem KEMER
>|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing th
e
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. I
f
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especiall
y
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduc
e
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.sql
if we have a table with huge amount of data in it
how much performance loss will occur (%) , if we use rowlock in our queries
on that table
Thanks in advance
Erdem KEMERThe only way to tell is to test... Most people let SQLs optimizer choose the
locking unless you are seeing some particular problem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"erdem" <erdem@.kulube.net> wrote in message
news:%23DIxxKlcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our
> queries on that table
> Thanks in advance
> Erdem KEMER
>|||erdem,
As Wayne suggested, I would leave the locking hints out unless you are
experiencing poor index selection from the optimiser, or poor
performance. If row locks become too expensive, SQL Server will escalate
to a table lock. If you are finding that row locks are too expensive and
table locks do not cut it, try specifying page locks. I would have
thought this to be unnecessary though.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
erdem wrote:
> hi everone
> if we have a table with huge amount of data in it
> how much performance loss will occur (%) , if we use rowlock in our querie
s
> on that table
> Thanks in advance
> Erdem KEMER
>|||As another poster already pointed out, you shouldn't use locking hints
without a very good reason; SQL Server does a pretty good job of choosing th
e
proper lock for the job.
But you can estimate the performance impact if you know your row size and
the number of records being updated in each query. For instance, assume
rows of 800 bytes (incl. overhead), so 10 rows fit on each 8K data page. I
f
your update affects one hundred rows, then row locking would incur 10X the
overhead as page locking (and 100X the overhead of a table lock). That
doesn't mean ten times slower, as lock overhead is only a fraction of the
total work done in an update, but the impact is still significant, especiall
y
on a cpu-bound server.
Rowlocks increase concurrency though, so the performance of other queries
running may increase substantially. In the case above, rowlocks would reduc
e
lock contention by other processes by (roughly) 90%. Of course, if the
server is thrashing mightily trying to acquire and release tens of thousands
of rowlocks, all queries will slow down, and it might wind up being slower
after all.
This is why SQL Server usually does a better job of choosing the locks than
we do. It makes a decision based on the actual size of the update, current
available memory, etc. Overriding this is useful only in rare cases.sql
RowGuid
Hi
What impact is there in setting a unqiueidentifier primary key to be a row guid?
How does this impact performance?
How does this impact data file size?
Does it impact anything else?
Thanks
Hi,I guess you don′t want to open up taht religious discussion, right ? There were many, many, many discussions already about this out there which can be find googling / MSNning around:
http://www.google.de/search?hl=de&q=GUID+Primary+Key&meta=
There are really many opinions out there, for me (for my personal opinion) you should avoid them, as long as you don′t really need them, like for distributed applications)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
Hi Jens
Thanks for the response but I wasn't asking if I should use a UNIQUEIDENTIFIER as a primary key.
I was just wondering what the impact was in setting the rowguid flag.
For example...
CREATE TABLE Schema.Table (Id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT PTableId PRIMARY KEY NONCLUSTERED WITH FILLFACTOR=60)
CREATE TABLE Schema.Table (Id UNIQUEIDENTIFIER NOT NULL CONSTRAINT PTableId PRIMARY KEY NONCLUSTERED WITH FILLFACTOR=60)
sqlrow-fetch question
Hi everyone,
I ran Profiler to verify why users where complaining about the bad
performance of our web application. I saw thousands of continuous
cursoropens, fetch and close. I googled it and came across an article that
tackled about cursors. It mentioned about row-fetch. He mentioned that if
row-fetch is 1, it is very inefficient. I would like to know how I can get
the current value of row-fetch and how to modify it.
And also, am I correct that server-side api cursors are cpu-intensive?
Any help will be greatly appreciated.
NeilThe FetchBufferSize property can be set to increase the rows to fetchin a
single operation. The default is 100 unless the data source uses binary
large object (BLOB) storage, in that case the FetchBufferSize is ignored and
1 is used instead. So it really depends on the data you are querying.
Question. Is your web app calling a stored proc that has a cursor, calling a
tree of sub cursors, or your web app calling a recordset, then making
queries from the results set, etc?
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:efJy9nEqEHA.3592@.TK2MSFTNGP14.phx.gbl...
> Hi everyone,
> I ran Profiler to verify why users where complaining about the bad
> performance of our web application. I saw thousands of continuous
> cursoropens, fetch and close. I googled it and came across an article that
> tackled about cursors. It mentioned about row-fetch. He mentioned that if
> row-fetch is 1, it is very inefficient. I would like to know how I can get
> the current value of row-fetch and how to modify it.
> And also, am I correct that server-side api cursors are cpu-intensive?
> Any help will be greatly appreciated.
> Neil
>|||Hi Dwinter,
I will have to dig in to the web-app because it is a third party
application.
I can ask the vendor about this.
What if it was calling a stored proc that has a cursor, calling a tree of
sub cursors, or your web app calling a recordset?
You can also read my reply to the other post today. This question is also
related to that. It's because, I am trying to troubleshoot an application
performance issue.
Thanks for you help.
Neil
"DWinter" <dwinter@.attbi.com> wrote in message
news:uo6fIzEqEHA.3708@.TK2MSFTNGP10.phx.gbl...
> The FetchBufferSize property can be set to increase the rows to fetchin a
> single operation. The default is 100 unless the data source uses binary
> large object (BLOB) storage, in that case the FetchBufferSize is ignored
> and
> 1 is used instead. So it really depends on the data you are querying.
> Question. Is your web app calling a stored proc that has a cursor, calling
> a
> tree of sub cursors, or your web app calling a recordset, then making
> queries from the results set, etc?
> "Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
> news:efJy9nEqEHA.3592@.TK2MSFTNGP14.phx.gbl...
>> Hi everyone,
>> I ran Profiler to verify why users where complaining about the bad
>> performance of our web application. I saw thousands of continuous
>> cursoropens, fetch and close. I googled it and came across an article
>> that
>> tackled about cursors. It mentioned about row-fetch. He mentioned that if
>> row-fetch is 1, it is very inefficient. I would like to know how I can
>> get
>> the current value of row-fetch and how to modify it.
>> And also, am I correct that server-side api cursors are cpu-intensive?
>> Any help will be greatly appreciated.
>> Neil
>>
>
I ran Profiler to verify why users where complaining about the bad
performance of our web application. I saw thousands of continuous
cursoropens, fetch and close. I googled it and came across an article that
tackled about cursors. It mentioned about row-fetch. He mentioned that if
row-fetch is 1, it is very inefficient. I would like to know how I can get
the current value of row-fetch and how to modify it.
And also, am I correct that server-side api cursors are cpu-intensive?
Any help will be greatly appreciated.
NeilThe FetchBufferSize property can be set to increase the rows to fetchin a
single operation. The default is 100 unless the data source uses binary
large object (BLOB) storage, in that case the FetchBufferSize is ignored and
1 is used instead. So it really depends on the data you are querying.
Question. Is your web app calling a stored proc that has a cursor, calling a
tree of sub cursors, or your web app calling a recordset, then making
queries from the results set, etc?
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:efJy9nEqEHA.3592@.TK2MSFTNGP14.phx.gbl...
> Hi everyone,
> I ran Profiler to verify why users where complaining about the bad
> performance of our web application. I saw thousands of continuous
> cursoropens, fetch and close. I googled it and came across an article that
> tackled about cursors. It mentioned about row-fetch. He mentioned that if
> row-fetch is 1, it is very inefficient. I would like to know how I can get
> the current value of row-fetch and how to modify it.
> And also, am I correct that server-side api cursors are cpu-intensive?
> Any help will be greatly appreciated.
> Neil
>|||Hi Dwinter,
I will have to dig in to the web-app because it is a third party
application.
I can ask the vendor about this.
What if it was calling a stored proc that has a cursor, calling a tree of
sub cursors, or your web app calling a recordset?
You can also read my reply to the other post today. This question is also
related to that. It's because, I am trying to troubleshoot an application
performance issue.
Thanks for you help.
Neil
"DWinter" <dwinter@.attbi.com> wrote in message
news:uo6fIzEqEHA.3708@.TK2MSFTNGP10.phx.gbl...
> The FetchBufferSize property can be set to increase the rows to fetchin a
> single operation. The default is 100 unless the data source uses binary
> large object (BLOB) storage, in that case the FetchBufferSize is ignored
> and
> 1 is used instead. So it really depends on the data you are querying.
> Question. Is your web app calling a stored proc that has a cursor, calling
> a
> tree of sub cursors, or your web app calling a recordset, then making
> queries from the results set, etc?
> "Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
> news:efJy9nEqEHA.3592@.TK2MSFTNGP14.phx.gbl...
>> Hi everyone,
>> I ran Profiler to verify why users where complaining about the bad
>> performance of our web application. I saw thousands of continuous
>> cursoropens, fetch and close. I googled it and came across an article
>> that
>> tackled about cursors. It mentioned about row-fetch. He mentioned that if
>> row-fetch is 1, it is very inefficient. I would like to know how I can
>> get
>> the current value of row-fetch and how to modify it.
>> And also, am I correct that server-side api cursors are cpu-intensive?
>> Any help will be greatly appreciated.
>> Neil
>>
>
Friday, March 23, 2012
Row performance
Hi.
I have a simple question.
What is the maximum number of rows in a table before MS SQL takes more
time to execute a query or the performance will slow down ' Is it
possible that the number is 1 000 000 ? If I remember, I saw that on
the web but I'm not sure. Consider that my table have been indexed.
Am I right or wrong '
Thanks.
Jonathan Chretien
Analyst/ProgrammerI would also expect for a non-indexed table, that performance would scale
linearly corresponding directly to the number of records in the table.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
news:3F27BEA0.41743AB0@.amisco.moc...
> Hi.
> I have a simple question.
> What is the maximum number of rows in a table before MS SQL takes more
> time to execute a query or the performance will slow down ' Is it
> possible that the number is 1 000 000 ? If I remember, I saw that on
> the web but I'm not sure. Consider that my table have been indexed.
> Am I right or wrong '
>
> Thanks.
>
> Jonathan Chretien
> Analyst/Programmer
>|||Hi.
My table is correctly indexed. I have only one index and my query is base on the
same field of my index. This table come from an AS400 and when I execute this
query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
then my MS SQL Server. I execute the query when the server is not load,
approximatly an average of 2 to 5% and the maximum peak that I receive from my
SQL Server is 50%.
Specification of my MS SQL Server:
Version: MS SQL Server 7.0
CPU: 2 PII Xeon 550
Memory: 512 meg
Disk: 40 gig
Database size 1.6 gig
Avg CPU utilization: Between 10 to 20 %
Jonathan Chretien
Analyst/Programmer
Wayne Snyder a écrit :
> I would also expect for a non-indexed table, that performance would scale
> linearly corresponding directly to the number of records in the table.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it community
> of SQL Server professionals.
> www.sqlpass.org
> "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> news:3F27BEA0.41743AB0@.amisco.moc...
> > Hi.
> >
> > I have a simple question.
> >
> > What is the maximum number of rows in a table before MS SQL takes more
> > time to execute a query or the performance will slow down ' Is it
> > possible that the number is 1 000 000 ? If I remember, I saw that on
> > the web but I'm not sure. Consider that my table have been indexed.
> >
> > Am I right or wrong '
> >
> >
> > Thanks.
> >
> >
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >|||Yes.
Query plan is using my index and have a cost of 81%.
Jonathan Chretien
Analyst/Programmer
Gert-Jan Strik a écrit :
> Check the execution plan to see if SQL-Server is actually using the
> index. There might be some reason why the index is disqualified. For
> example when your statistics are out of date, or when your predicate is
> not SARGuable.
> Hope this helps,
> Gert-Jan
> Jonathan Chretien wrote:
> >
> > Hi.
> >
> > My table is correctly indexed. I have only one index and my query is base on the
> > same field of my index. This table come from an AS400 and when I execute this
> > query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
> > my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
> > then my MS SQL Server. I execute the query when the server is not load,
> > approximatly an average of 2 to 5% and the maximum peak that I receive from my
> > SQL Server is 50%.
> >
> > Specification of my MS SQL Server:
> > Version: MS SQL Server 7.0
> > CPU: 2 PII Xeon 550
> > Memory: 512 meg
> > Disk: 40 gig
> > Database size 1.6 gig
> > Avg CPU utilization: Between 10 to 20 %
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >
> > Wayne Snyder a écrit :
> >
> > > I would also expect for a non-indexed table, that performance would scale
> > > linearly corresponding directly to the number of records in the table.
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > www.computeredservices.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it community
> > > of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> > > news:3F27BEA0.41743AB0@.amisco.moc...
> > > > Hi.
> > > >
> > > > I have a simple question.
> > > >
> > > > What is the maximum number of rows in a table before MS SQL takes more
> > > > time to execute a query or the performance will slow down ' Is it
> > > > possible that the number is 1 000 000 ? If I remember, I saw that on
> > > > the web but I'm not sure. Consider that my table have been indexed.
> > > >
> > > > Am I right or wrong '
> > > >
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >
> > > > Jonathan Chretien
> > > > Analyst/Programmer
> > > >|||Jonathan,
1. Is it performing an index SEEK (good) or index SCAN (bad)?
2. Is it a clustered or non-clustered index?
Gert-Jan
Jonathan Chretien wrote:
> Yes.
> Query plan is using my index and have a cost of 81%.
> Jonathan Chretien
> Analyst/Programmer
> Gert-Jan Strik a écrit :
> > Check the execution plan to see if SQL-Server is actually using the
> > index. There might be some reason why the index is disqualified. For
> > example when your statistics are out of date, or when your predicate is
> > not SARGuable.
> >
> > Hope this helps,
> > Gert-Jan
> >
> > Jonathan Chretien wrote:
> > >
> > > Hi.
> > >
> > > My table is correctly indexed. I have only one index and my query is base on the
> > > same field of my index. This table come from an AS400 and when I execute this
> > > query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
> > > my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
> > > then my MS SQL Server. I execute the query when the server is not load,
> > > approximatly an average of 2 to 5% and the maximum peak that I receive from my
> > > SQL Server is 50%.
> > >
> > > Specification of my MS SQL Server:
> > > Version: MS SQL Server 7.0
> > > CPU: 2 PII Xeon 550
> > > Memory: 512 meg
> > > Disk: 40 gig
> > > Database size 1.6 gig
> > > Avg CPU utilization: Between 10 to 20 %
> > >
> > > Jonathan Chretien
> > > Analyst/Programmer
> > >
> > > Wayne Snyder a écrit :
> > >
> > > > I would also expect for a non-indexed table, that performance would scale
> > > > linearly corresponding directly to the number of records in the table.
> > > >
> > > > --
> > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > > www.computeredservices.com
> > > > (Please respond only to the newsgroups.)
> > > >
> > > > I support the Professional Association of SQL Server (PASS) and it community
> > > > of SQL Server professionals.
> > > > www.sqlpass.org
> > > >
> > > > "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> > > > news:3F27BEA0.41743AB0@.amisco.moc...
> > > > > Hi.
> > > > >
> > > > > I have a simple question.
> > > > >
> > > > > What is the maximum number of rows in a table before MS SQL takes more
> > > > > time to execute a query or the performance will slow down ' Is it
> > > > > possible that the number is 1 000 000 ? If I remember, I saw that on
> > > > > the web but I'm not sure. Consider that my table have been indexed.
> > > > >
> > > > > Am I right or wrong '
> > > > >
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > > >
> > > > > Jonathan Chretien
> > > > > Analyst/Programmer
> > > > >
I have a simple question.
What is the maximum number of rows in a table before MS SQL takes more
time to execute a query or the performance will slow down ' Is it
possible that the number is 1 000 000 ? If I remember, I saw that on
the web but I'm not sure. Consider that my table have been indexed.
Am I right or wrong '
Thanks.
Jonathan Chretien
Analyst/ProgrammerI would also expect for a non-indexed table, that performance would scale
linearly corresponding directly to the number of records in the table.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
news:3F27BEA0.41743AB0@.amisco.moc...
> Hi.
> I have a simple question.
> What is the maximum number of rows in a table before MS SQL takes more
> time to execute a query or the performance will slow down ' Is it
> possible that the number is 1 000 000 ? If I remember, I saw that on
> the web but I'm not sure. Consider that my table have been indexed.
> Am I right or wrong '
>
> Thanks.
>
> Jonathan Chretien
> Analyst/Programmer
>|||Hi.
My table is correctly indexed. I have only one index and my query is base on the
same field of my index. This table come from an AS400 and when I execute this
query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
then my MS SQL Server. I execute the query when the server is not load,
approximatly an average of 2 to 5% and the maximum peak that I receive from my
SQL Server is 50%.
Specification of my MS SQL Server:
Version: MS SQL Server 7.0
CPU: 2 PII Xeon 550
Memory: 512 meg
Disk: 40 gig
Database size 1.6 gig
Avg CPU utilization: Between 10 to 20 %
Jonathan Chretien
Analyst/Programmer
Wayne Snyder a écrit :
> I would also expect for a non-indexed table, that performance would scale
> linearly corresponding directly to the number of records in the table.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it community
> of SQL Server professionals.
> www.sqlpass.org
> "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> news:3F27BEA0.41743AB0@.amisco.moc...
> > Hi.
> >
> > I have a simple question.
> >
> > What is the maximum number of rows in a table before MS SQL takes more
> > time to execute a query or the performance will slow down ' Is it
> > possible that the number is 1 000 000 ? If I remember, I saw that on
> > the web but I'm not sure. Consider that my table have been indexed.
> >
> > Am I right or wrong '
> >
> >
> > Thanks.
> >
> >
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >|||Yes.
Query plan is using my index and have a cost of 81%.
Jonathan Chretien
Analyst/Programmer
Gert-Jan Strik a écrit :
> Check the execution plan to see if SQL-Server is actually using the
> index. There might be some reason why the index is disqualified. For
> example when your statistics are out of date, or when your predicate is
> not SARGuable.
> Hope this helps,
> Gert-Jan
> Jonathan Chretien wrote:
> >
> > Hi.
> >
> > My table is correctly indexed. I have only one index and my query is base on the
> > same field of my index. This table come from an AS400 and when I execute this
> > query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
> > my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
> > then my MS SQL Server. I execute the query when the server is not load,
> > approximatly an average of 2 to 5% and the maximum peak that I receive from my
> > SQL Server is 50%.
> >
> > Specification of my MS SQL Server:
> > Version: MS SQL Server 7.0
> > CPU: 2 PII Xeon 550
> > Memory: 512 meg
> > Disk: 40 gig
> > Database size 1.6 gig
> > Avg CPU utilization: Between 10 to 20 %
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >
> > Wayne Snyder a écrit :
> >
> > > I would also expect for a non-indexed table, that performance would scale
> > > linearly corresponding directly to the number of records in the table.
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > www.computeredservices.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it community
> > > of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> > > news:3F27BEA0.41743AB0@.amisco.moc...
> > > > Hi.
> > > >
> > > > I have a simple question.
> > > >
> > > > What is the maximum number of rows in a table before MS SQL takes more
> > > > time to execute a query or the performance will slow down ' Is it
> > > > possible that the number is 1 000 000 ? If I remember, I saw that on
> > > > the web but I'm not sure. Consider that my table have been indexed.
> > > >
> > > > Am I right or wrong '
> > > >
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >
> > > > Jonathan Chretien
> > > > Analyst/Programmer
> > > >|||Jonathan,
1. Is it performing an index SEEK (good) or index SCAN (bad)?
2. Is it a clustered or non-clustered index?
Gert-Jan
Jonathan Chretien wrote:
> Yes.
> Query plan is using my index and have a cost of 81%.
> Jonathan Chretien
> Analyst/Programmer
> Gert-Jan Strik a écrit :
> > Check the execution plan to see if SQL-Server is actually using the
> > index. There might be some reason why the index is disqualified. For
> > example when your statistics are out of date, or when your predicate is
> > not SARGuable.
> >
> > Hope this helps,
> > Gert-Jan
> >
> > Jonathan Chretien wrote:
> > >
> > > Hi.
> > >
> > > My table is correctly indexed. I have only one index and my query is base on the
> > > same field of my index. This table come from an AS400 and when I execute this
> > > query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
> > > my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
> > > then my MS SQL Server. I execute the query when the server is not load,
> > > approximatly an average of 2 to 5% and the maximum peak that I receive from my
> > > SQL Server is 50%.
> > >
> > > Specification of my MS SQL Server:
> > > Version: MS SQL Server 7.0
> > > CPU: 2 PII Xeon 550
> > > Memory: 512 meg
> > > Disk: 40 gig
> > > Database size 1.6 gig
> > > Avg CPU utilization: Between 10 to 20 %
> > >
> > > Jonathan Chretien
> > > Analyst/Programmer
> > >
> > > Wayne Snyder a écrit :
> > >
> > > > I would also expect for a non-indexed table, that performance would scale
> > > > linearly corresponding directly to the number of records in the table.
> > > >
> > > > --
> > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > > www.computeredservices.com
> > > > (Please respond only to the newsgroups.)
> > > >
> > > > I support the Professional Association of SQL Server (PASS) and it community
> > > > of SQL Server professionals.
> > > > www.sqlpass.org
> > > >
> > > > "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> > > > news:3F27BEA0.41743AB0@.amisco.moc...
> > > > > Hi.
> > > > >
> > > > > I have a simple question.
> > > > >
> > > > > What is the maximum number of rows in a table before MS SQL takes more
> > > > > time to execute a query or the performance will slow down ' Is it
> > > > > possible that the number is 1 000 000 ? If I remember, I saw that on
> > > > > the web but I'm not sure. Consider that my table have been indexed.
> > > > >
> > > > > Am I right or wrong '
> > > > >
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > > >
> > > > > Jonathan Chretien
> > > > > Analyst/Programmer
> > > > >
Wednesday, March 21, 2012
Row Lock On Update Statement
What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,
You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,
You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
Row Lock On Update Statement
What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
Row Lock On Update Statement
What is the correct syntax to create a row lock for an update statement that
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
updates only a single row in a table? This should help performance since it
does not have to lock the table to update a single row.
Thank You,You don't place lock on it, SQL Server will and it won't lock the table for
it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>|||If your update's WHERE clause uses a key, you should not see the entire
table getting locked. Is that what you're seeing?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:6C028E2D-EACD-4376-B2D1-B209B07233FB@.microsoft.com...
> What is the correct syntax to create a row lock for an update statement
> that
> updates only a single row in a table? This should help performance since
> it
> does not have to lock the table to update a single row.
> Thank You,
>
Monday, March 12, 2012
row filter where clause (busy db performance hit?)
Hello.
I need to setup a filter for a really busy db
that we are replicating. I was wondering what
type of filter to use. I am afraid that the
where clause of a row filter will create a major
performance hit. I was looking into dts and
Transformable Subscriptions but am not real clear
if that would be better.
any advice would be appreciated.
thanks,
-comb
Would this be a static filter or a dynamic filter, ie where store_id=2
(static) or where store_id=hostname() (dynamic)?
There is more processing involved in using filters. The impact may vary
widely.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1c0e66c5405f97bc9896ef@.news.newsreader.co m...
> Hello.
> I need to setup a filter for a really busy db
> that we are replicating. I was wondering what
> type of filter to use. I am afraid that the
> where clause of a row filter will create a major
> performance hit. I was looking into dts and
> Transformable Subscriptions but am not real clear
> if that would be better.
> any advice would be appreciated.
> thanks,
> -comb
|||In article <OmLTYxk0EHA.1932
@.TK2MSFTNGP09.phx.gbl>, hilary.cotter@.gmail.com
says...
> Would this be a static filter or a dynamic filter, ie where store_id=2
> (static) or where store_id=hostname() (dynamic)?
> There is more processing involved in using filters. The impact may vary
> widely.
>
it's static. where fkstoreid in (5001,5002)
like that.
-comb
|||is fkstoreid indexed?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1c0e87f9669ff2889896f0@.news.newsreader.co m...
> In article <OmLTYxk0EHA.1932
> @.TK2MSFTNGP09.phx.gbl>, hilary.cotter@.gmail.com
> says...
> it's static. where fkstoreid in (5001,5002)
> like that.
> -comb
|||In article <#qRjNSl0EHA.3072
@.TK2MSFTNGP11.phx.gbl>, hilary.cotter@.gmail.com
says...
> is fkstoreid indexed?
>
yes
|||This filter probably is optimal.
You might want to look at replicating the execution of stored procedures -
this can lead to radical performance increases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1c0ea5cf51e7c2e59896f1@.news.newsreader.co m...
> In article <#qRjNSl0EHA.3072
> @.TK2MSFTNGP11.phx.gbl>, hilary.cotter@.gmail.com
> says...
> yes
|||In article <OP2$6Gv0EHA.3368
@.TK2MSFTNGP10.phx.gbl>, hilary.cotter@.gmail.com
says...
> This filter probably is optimal.
> You might want to look at replicating the execution of stored procedures -
> this can lead to radical performance increases.
>
thanks,
-comb
I need to setup a filter for a really busy db
that we are replicating. I was wondering what
type of filter to use. I am afraid that the
where clause of a row filter will create a major
performance hit. I was looking into dts and
Transformable Subscriptions but am not real clear
if that would be better.
any advice would be appreciated.
thanks,
-comb
Would this be a static filter or a dynamic filter, ie where store_id=2
(static) or where store_id=hostname() (dynamic)?
There is more processing involved in using filters. The impact may vary
widely.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1c0e66c5405f97bc9896ef@.news.newsreader.co m...
> Hello.
> I need to setup a filter for a really busy db
> that we are replicating. I was wondering what
> type of filter to use. I am afraid that the
> where clause of a row filter will create a major
> performance hit. I was looking into dts and
> Transformable Subscriptions but am not real clear
> if that would be better.
> any advice would be appreciated.
> thanks,
> -comb
|||In article <OmLTYxk0EHA.1932
@.TK2MSFTNGP09.phx.gbl>, hilary.cotter@.gmail.com
says...
> Would this be a static filter or a dynamic filter, ie where store_id=2
> (static) or where store_id=hostname() (dynamic)?
> There is more processing involved in using filters. The impact may vary
> widely.
>
it's static. where fkstoreid in (5001,5002)
like that.
-comb
|||is fkstoreid indexed?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1c0e87f9669ff2889896f0@.news.newsreader.co m...
> In article <OmLTYxk0EHA.1932
> @.TK2MSFTNGP09.phx.gbl>, hilary.cotter@.gmail.com
> says...
> it's static. where fkstoreid in (5001,5002)
> like that.
> -comb
|||In article <#qRjNSl0EHA.3072
@.TK2MSFTNGP11.phx.gbl>, hilary.cotter@.gmail.com
says...
> is fkstoreid indexed?
>
yes
|||This filter probably is optimal.
You might want to look at replicating the execution of stored procedures -
this can lead to radical performance increases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1c0ea5cf51e7c2e59896f1@.news.newsreader.co m...
> In article <#qRjNSl0EHA.3072
> @.TK2MSFTNGP11.phx.gbl>, hilary.cotter@.gmail.com
> says...
> yes
|||In article <OP2$6Gv0EHA.3368
@.TK2MSFTNGP10.phx.gbl>, hilary.cotter@.gmail.com
says...
> This filter probably is optimal.
> You might want to look at replicating the execution of stored procedures -
> this can lead to radical performance increases.
>
thanks,
-comb
Saturday, February 25, 2012
Round date on a chart
Hello!
I'm collecting performance counters from few servers.
They are collecting every 10 minutes. After that i need to create
day-report.But when i put "processor use" on Y and time on X, on X i've got
a forest of small dates.
I need only fuul hours (8, 10, 12 etc). How can i do this?
I;m thinking about round it, but what syntax is for it?
And where to type it?
LeszekUse Date.Parse
Andy Potter|||I think that there is a problem with only showing that dates.
When i put (under chart) a Computername, then i have information about
processor for each computer (labels with computername exists).
But when i put date there, i see only my text (without dates).
Where can be a problem?
Type conversion?
I just need to display hours...
Uzytkownik "Potter" <drewpotter@.gmail.com> napisal w wiadomosci
news:1137425277.468665.280170@.g14g2000cwa.googlegroups.com...
> Use Date.Parse
> Andy Potter
>|||I'm not really following you. You're concatenating text and a datetime
value? Have you tried doing a .ToString on your date value?
Andy Potter|||If I get you right, you have too many datas for a day and you only want to
"print" a label in the chart every hour, not every 10 minutes. I had a
similar situation way back, and the date/time labels at the bottom of the
charts got very small and unreadable.
My first try was to show only every 6th label by leaving 5 blank and only
show 1, but Reporting Services fully drawn the blank labels over my real ones
which were unreadable as well. Finally I create the average of the 6 values
on the SQL server and used this aggregate for displaying (To catch suspicious
values I also added a min/max value as well).
The SQL looked like:
select min(Value) as minValue, max(Value) as maxValue, avg(Value) as avgValue
, min ( cast(floor(cast(tmDate as float) * 24) / 24 as smalldatetime) ) as
tmDate
from
(
select Value, tmDate from tabMeasures where tmDate between @.tmStart and
@.tmEnd
)
group by datepart(hour, tmDate)
To hide the seconds in the chart I used to modify the label field from
=Fields!tmDate.Value
to
=Fields.tmDate.Value.ToShortTimeString()
"Leszek" wrote:
> Hello!
> I'm collecting performance counters from few servers.
> They are collecting every 10 minutes. After that i need to create
> day-report.But when i put "processor use" on Y and time on X, on X i've got
> a forest of small dates.
> I need only fuul hours (8, 10, 12 etc). How can i do this?
> I;m thinking about round it, but what syntax is for it?
> And where to type it?
> Leszek
>
>|||Now i have different problem:
When i see my report, i see only one date. Other are like 38748, 37568 etc.
It looks like converted date to other type of values?
Sometimes i see two dates and other date values are 38732, 38768 :/
Where could be a problem?
And my other problem - how can i dynamically generate report, where i will
get as much charts, as i have comupters in my database.column?
U¿ytkownik "GreyMana" <GreyMana@.discussions.microsoft.com> napisa³ w
wiadomo¶ci news:D4821781-CE04-4520-9070-4CA661F877AE@.microsoft.com...
> If I get you right, you have too many datas for a day and you only want to
> "print" a label in the chart every hour, not every 10 minutes. I had a
> similar situation way back, and the date/time labels at the bottom of the
> charts got very small and unreadable.
> My first try was to show only every 6th label by leaving 5 blank and only
> show 1, but Reporting Services fully drawn the blank labels over my real
> ones
> which were unreadable as well. Finally I create the average of the 6
> values
> on the SQL server and used this aggregate for displaying (To catch
> suspicious
> values I also added a min/max value as well).
> The SQL looked like:
> select min(Value) as minValue, max(Value) as maxValue, avg(Value) as
> avgValue
> , min ( cast(floor(cast(tmDate as float) * 24) / 24 as smalldatetime) ) as
> tmDate
> from
> (
> select Value, tmDate from tabMeasures where tmDate between @.tmStart and
> @.tmEnd
> )
> group by datepart(hour, tmDate)
> To hide the seconds in the chart I used to modify the label field from
> =Fields!tmDate.Value
> to
> =Fields.tmDate.Value.ToShortTimeString()
>
> "Leszek" wrote:
>> Hello!
>> I'm collecting performance counters from few servers.
>> They are collecting every 10 minutes. After that i need to create
>> day-report.But when i put "processor use" on Y and time on X, on X i've
>> got
>> a forest of small dates.
>> I need only fuul hours (8, 10, 12 etc). How can i do this?
>> I;m thinking about round it, but what syntax is for it?
>> And where to type it?
>> Leszek
>>
I'm collecting performance counters from few servers.
They are collecting every 10 minutes. After that i need to create
day-report.But when i put "processor use" on Y and time on X, on X i've got
a forest of small dates.
I need only fuul hours (8, 10, 12 etc). How can i do this?
I;m thinking about round it, but what syntax is for it?
And where to type it?
LeszekUse Date.Parse
Andy Potter|||I think that there is a problem with only showing that dates.
When i put (under chart) a Computername, then i have information about
processor for each computer (labels with computername exists).
But when i put date there, i see only my text (without dates).
Where can be a problem?
Type conversion?
I just need to display hours...
Uzytkownik "Potter" <drewpotter@.gmail.com> napisal w wiadomosci
news:1137425277.468665.280170@.g14g2000cwa.googlegroups.com...
> Use Date.Parse
> Andy Potter
>|||I'm not really following you. You're concatenating text and a datetime
value? Have you tried doing a .ToString on your date value?
Andy Potter|||If I get you right, you have too many datas for a day and you only want to
"print" a label in the chart every hour, not every 10 minutes. I had a
similar situation way back, and the date/time labels at the bottom of the
charts got very small and unreadable.
My first try was to show only every 6th label by leaving 5 blank and only
show 1, but Reporting Services fully drawn the blank labels over my real ones
which were unreadable as well. Finally I create the average of the 6 values
on the SQL server and used this aggregate for displaying (To catch suspicious
values I also added a min/max value as well).
The SQL looked like:
select min(Value) as minValue, max(Value) as maxValue, avg(Value) as avgValue
, min ( cast(floor(cast(tmDate as float) * 24) / 24 as smalldatetime) ) as
tmDate
from
(
select Value, tmDate from tabMeasures where tmDate between @.tmStart and
@.tmEnd
)
group by datepart(hour, tmDate)
To hide the seconds in the chart I used to modify the label field from
=Fields!tmDate.Value
to
=Fields.tmDate.Value.ToShortTimeString()
"Leszek" wrote:
> Hello!
> I'm collecting performance counters from few servers.
> They are collecting every 10 minutes. After that i need to create
> day-report.But when i put "processor use" on Y and time on X, on X i've got
> a forest of small dates.
> I need only fuul hours (8, 10, 12 etc). How can i do this?
> I;m thinking about round it, but what syntax is for it?
> And where to type it?
> Leszek
>
>|||Now i have different problem:
When i see my report, i see only one date. Other are like 38748, 37568 etc.
It looks like converted date to other type of values?
Sometimes i see two dates and other date values are 38732, 38768 :/
Where could be a problem?
And my other problem - how can i dynamically generate report, where i will
get as much charts, as i have comupters in my database.column?
U¿ytkownik "GreyMana" <GreyMana@.discussions.microsoft.com> napisa³ w
wiadomo¶ci news:D4821781-CE04-4520-9070-4CA661F877AE@.microsoft.com...
> If I get you right, you have too many datas for a day and you only want to
> "print" a label in the chart every hour, not every 10 minutes. I had a
> similar situation way back, and the date/time labels at the bottom of the
> charts got very small and unreadable.
> My first try was to show only every 6th label by leaving 5 blank and only
> show 1, but Reporting Services fully drawn the blank labels over my real
> ones
> which were unreadable as well. Finally I create the average of the 6
> values
> on the SQL server and used this aggregate for displaying (To catch
> suspicious
> values I also added a min/max value as well).
> The SQL looked like:
> select min(Value) as minValue, max(Value) as maxValue, avg(Value) as
> avgValue
> , min ( cast(floor(cast(tmDate as float) * 24) / 24 as smalldatetime) ) as
> tmDate
> from
> (
> select Value, tmDate from tabMeasures where tmDate between @.tmStart and
> @.tmEnd
> )
> group by datepart(hour, tmDate)
> To hide the seconds in the chart I used to modify the label field from
> =Fields!tmDate.Value
> to
> =Fields.tmDate.Value.ToShortTimeString()
>
> "Leszek" wrote:
>> Hello!
>> I'm collecting performance counters from few servers.
>> They are collecting every 10 minutes. After that i need to create
>> day-report.But when i put "processor use" on Y and time on X, on X i've
>> got
>> a forest of small dates.
>> I need only fuul hours (8, 10, 12 etc). How can i do this?
>> I;m thinking about round it, but what syntax is for it?
>> And where to type it?
>> Leszek
>>
Subscribe to:
Posts (Atom)