I'm learning about indexes and performance, looking at some of my queries and
comparing them with the Index Tuning Wizard (ITW) to figure things out.
BTW, is there a way to use the Profiler to "get the distinct queries [on
table x]"? I would really like to see every query on a table when looking at
the indexes on them, but I can't figure out how to do this.
Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an int
fkey (security), two floats (one unused), a type, a date and a varchar notes
(typically empty). Data is unique on fkey, type and date. We insert about 500
rows a day in one big batch, so insert performance isn't _too_ much of an
issue, but because it's a batch you tend to notice it.
Indexes are currently on the pkey (clustered unique), fkey, type and date.
There is also a constraint on fkey/type/date.
My GUESS is that the common types of queries are...
1) price for this security and this date
2) prices for this date
3) prices for this security
I don't really know which ones are common or not, which is why I was asking
how I could find this out.
dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
this makes it an excellent target for an index. In reality there are only 5
values in total, and the VAST majority of the records are a single one. Can
anyone explain this output? Also, why didn't the ITW suggest removing it,
when it obviously will never be used?
ITW also suggested removing the index on date. This seems suspect, but I
can't prove it. The reason it's suspect is that we would have roughtly 500
prices per date, and about the same number per security. This would seem to
imply that either index should be useful depending on the query. We tend to
GROUP BY on the security, in order to get the newest price per security,
which would also seem to imply that the date index would be useful -- is this
possible? Can it use the fkey to extract the unique securities, the date to
get the newest for all securities, and then hash match?
Basically ITW simply doesn't have enough information for me to make up my
mind. If I could see the queries, how often they occured, and what indexes
they used, then it would be useful. Now, however, I don't trust the numbers.
One last question about the compound constraint. Since the first column in
it is the fkey, should I instead make it an index and drop the fkey index?
What is the difference between a constraint and index in this context anyway?
I assume that the constraint doesn't do a table search for every insert, so
there's some sort of index that it's using anyway?
Maury
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7058F853-8697-4DBA-9225-3B544D2E59A4@.microsoft.com...
> I'm learning about indexes and performance, looking at some of my queries
> and
> comparing them with the Index Tuning Wizard (ITW) to figure things out.
> BTW, is there a way to use the Profiler to "get the distinct queries [on
> table x]"? I would really like to see every query on a table when looking
> at
> the indexes on them, but I can't figure out how to do this.
> Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an
> int
> fkey (security), two floats (one unused), a type, a date and a varchar
> notes
> (typically empty). Data is unique on fkey, type and date. We insert about
> 500
> rows a day in one big batch, so insert performance isn't _too_ much of an
> issue, but because it's a batch you tend to notice it.
> Indexes are currently on the pkey (clustered unique), fkey, type and date.
> There is also a constraint on fkey/type/date.
> My GUESS is that the common types of queries are...
> 1) price for this security and this date
> 2) prices for this date
> 3) prices for this security
> I don't really know which ones are common or not, which is why I was
> asking
> how I could find this out.
> dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
> this makes it an excellent target for an index. In reality there are only
> 5
> values in total, and the VAST majority of the records are a single one.
> Can
> anyone explain this output? Also, why didn't the ITW suggest removing it,
> when it obviously will never be used?
> ITW also suggested removing the index on date. This seems suspect, but I
> can't prove it. The reason it's suspect is that we would have roughtly 500
> prices per date, and about the same number per security. This would seem
> to
> imply that either index should be useful depending on the query. We tend
> to
> GROUP BY on the security, in order to get the newest price per security,
> which would also seem to imply that the date index would be useful -- is
> this
> possible? Can it use the fkey to extract the unique securities, the date
> to
> get the newest for all securities, and then hash match?
> Basically ITW simply doesn't have enough information for me to make up my
> mind. If I could see the queries, how often they occured, and what indexes
> they used, then it would be useful. Now, however, I don't trust the
> numbers.
> One last question about the compound constraint. Since the first column in
> it is the fkey, should I instead make it an index and drop the fkey index?
> What is the difference between a constraint and index in this context
> anyway?
A foreign key constraint will generate a check on insert and update to
guarantee that a related row exists in the primary key table. The
constraint does not store any data, and is no help at all in querying. In
generay you should support each foreign key constraint with an index on the
foreign key table on the foreign key columns.
Now. Your bigger question. What indexing scheme to use. First off since
you only insert 500 rows/day, and you do it in one big batch, you should
probably change your clustered index. You will need to run Profiler to
capture a typical query workload, and this will drive your choice of index.
I suspect that the rignt clustered index is a unique clustered index on
either (security,date,type) or (date,security,type). Which one will depend
on the query workload. Then you will have an index for your primary key,
and clustered index for your alternate key. Once you have that, rerun ITW
and see where you are.
> I assume that the constraint doesn't do a table search for every insert,
> so
> there's some sort of index that it's using anyway?
Yes it uses the primary key index on the PK table, but that doesn't help
your query.
David
|||I did respond, quite a bit, to another post you had, but what you are asking
is a pretty generic question, since we do not have specific enough
information here to give detailed repsones; so, about the best you are going
to get is a generic response...but that can be useful too.
1. Relational theory dictates that before an entity is evein 1NF (first
normal form) you MUST specify a Key. Any entity may have one or multiple
candidate keys. There may be a natural key. And, there may even be
complex, compound (composite) keys. One of these has to be the Primary Key.
The rest are alternate keys and should have Unique Key Constraints defined
on them. Oftentimes, the Primary Business Key is a lousy physical key; so,
a surrogate, Auto-Generated, IDENTITY is constructed then chosen as the
Primary Key, physically. This is fine, but keep in mind that that physical
database is just an implementation of a Logical Database Model that supports
a very specific Business Model and that NO surrogate key defines a business
key; thus, the term surrogate. The physical alternate keys, that model the
Business Keys, should always be constrained in order to enforce the logical
business rules.
2. As you go through the normalization process, you wll, hopefully, end up
at 3NF (third normal form), or higher, and a physical consequence of which
is that you will end up with dependent tables that must be constrained with
Foreign Key definitions. In the dependents, these FKCs should be indexed.
In the parent, they must be Uniquely constrained by a PKC or UCs. In the
child, they normally will be reasonably selectible, if not highly
selectible.
3. EVERY TABLE should have a clustered index defined. This is a purely
physical argument and is highly dependent on the SQL Server DBMS. Heaps,
which is what you get when a clustered index is NOT defined, perform
horibly, in terms of storage management and database maitenance. But many
have trouble choosing a proper definition. Here are some generic tips. The
attributes should not be modified but minimally at most. Most often you
would be better served with a single attribute as opposed to a composite
definition. Choose an attribute that is queried often, but by a range
mechanism as opposed to singleton selects (medium cardinality). Then,
finally, the attribute should be as narrow as possible, which is helped by
having single column definitions, but even those, numerics are better than
characters, and narrow characters are better than wider ones. You should
examine the alternate key definitions as possible clustered index candidates
as ALL clustered indexes use UNIQUENESS, either because they are defined as
unique, or because SQL Server appends a GUID to the attribute definition to
make them unique. So, choosing an already uniquely defined key does keep
the overhead down.
4. Now, go through your tables and determine which columns are highly
selectible (high cardinality), that is, if you were to query the table, with
a specific value, what fraction of the table rows would you expect to
return? The fewer, the better candidate for a non-clustered index that
attribute would be. At this stage, I would concentrate solely on single
attribute column indexes. The reason is that statistical histograms are
only stored for the most significant attribute; thus, it is very important
for that attribute to be highly selectible.
The only reason having a composite index would be of value would be becuase
what is known as a "covering" index. A clustered index is a covering index
by definition. This is because a covering index has all the data defining
the index stored on the index page. So, if first name is apart of a name
index, where the last name is the first defining attribute, after the
page(s) is located by a search on last name, SQL Server can also pull the
first name attribute from the index page itself instead of have to run a
bookmark lookup to the referenced data page. Not too many indexes can be
defined this way. A clustered index can because the entire data row is
stored on the clustered index page because, for a clustered index, the index
page IS the data page. Another example would be a non-clustered index but
where every table attribute is used to define it, but that would be highly
redundant. So, besides the clustered index, there are not to many
candidates that could be defined for a covering index.
Moreover, having many singleton indexes provides SQL Server an oportunity to
pull several indexes to perform a set intersection routine, which is far
more efficient than having any one index with composite values.
5. Finally, run sp_createstats against your database. This will create
single-column statistics against every table and every column that does not
already have a supporting index. This can help you determine missed columns
that have high cardinality that could be assisted by creating a
non-clustered index. These statistics also assist the Optimizer to quickly
reject low cardinality columns.
Well, I know this covered quite a bit of gound, some of it not too well, but
there is a ton of literature out there that goes into a lot of detail on
this subject. You need to dig into it. But, a word of caution, many of
these publications are just flat-out wrong. Take everything you read,
your's truely included, with some scepticism. Spend time in the Query
Analyzer. Examine the execution plans. Use hints to override the
Optimizer, not as a production practice, but to see where and how the
Optimizer behaves and why it is far more likely to pick an execution plan
than you are.
Lastly, always make sure your statistics are up to date before you begin any
analysis work. The Optimizer is only as good as these statistics are.
Sincerely,
Anthony Thomas
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7058F853-8697-4DBA-9225-3B544D2E59A4@.microsoft.com...
I'm learning about indexes and performance, looking at some of my queries
and
comparing them with the Index Tuning Wizard (ITW) to figure things out.
BTW, is there a way to use the Profiler to "get the distinct queries [on
table x]"? I would really like to see every query on a table when looking at
the indexes on them, but I can't figure out how to do this.
Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an int
fkey (security), two floats (one unused), a type, a date and a varchar notes
(typically empty). Data is unique on fkey, type and date. We insert about
500
rows a day in one big batch, so insert performance isn't _too_ much of an
issue, but because it's a batch you tend to notice it.
Indexes are currently on the pkey (clustered unique), fkey, type and date.
There is also a constraint on fkey/type/date.
My GUESS is that the common types of queries are...
1) price for this security and this date
2) prices for this date
3) prices for this security
I don't really know which ones are common or not, which is why I was asking
how I could find this out.
dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
this makes it an excellent target for an index. In reality there are only 5
values in total, and the VAST majority of the records are a single one. Can
anyone explain this output? Also, why didn't the ITW suggest removing it,
when it obviously will never be used?
ITW also suggested removing the index on date. This seems suspect, but I
can't prove it. The reason it's suspect is that we would have roughtly 500
prices per date, and about the same number per security. This would seem to
imply that either index should be useful depending on the query. We tend to
GROUP BY on the security, in order to get the newest price per security,
which would also seem to imply that the date index would be useful -- is
this
possible? Can it use the fkey to extract the unique securities, the date to
get the newest for all securities, and then hash match?
Basically ITW simply doesn't have enough information for me to make up my
mind. If I could see the queries, how often they occured, and what indexes
they used, then it would be useful. Now, however, I don't trust the numbers.
One last question about the compound constraint. Since the first column in
it is the fkey, should I instead make it an index and drop the fkey index?
What is the difference between a constraint and index in this context
anyway?
I assume that the constraint doesn't do a table search for every insert, so
there's some sort of index that it's using anyway?
Maury
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Wednesday, March 28, 2012
More index questions
I'm learning about indexes and performance, looking at some of my queries an
d
comparing them with the Index Tuning Wizard (ITW) to figure things out.
BTW, is there a way to use the Profiler to "get the distinct queries [on
table x]"? I would really like to see every query on a table when looking at
the indexes on them, but I can't figure out how to do this.
Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an int
fkey (security), two floats (one unused), a type, a date and a varchar notes
(typically empty). Data is unique on fkey, type and date. We insert about 50
0
rows a day in one big batch, so insert performance isn't _too_ much of an
issue, but because it's a batch you tend to notice it.
Indexes are currently on the pkey (clustered unique), fkey, type and date.
There is also a constraint on fkey/type/date.
My GUESS is that the common types of queries are...
1) price for this security and this date
2) prices for this date
3) prices for this security
I don't really know which ones are common or not, which is why I was asking
how I could find this out.
dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
this makes it an excellent target for an index. In reality there are only 5
values in total, and the VAST majority of the records are a single one. Can
anyone explain this output? Also, why didn't the ITW suggest removing it,
when it obviously will never be used?
ITW also suggested removing the index on date. This seems suspect, but I
can't prove it. The reason it's suspect is that we would have roughtly 500
prices per date, and about the same number per security. This would seem to
imply that either index should be useful depending on the query. We tend to
GROUP BY on the security, in order to get the newest price per security,
which would also seem to imply that the date index would be useful -- is thi
s
possible? Can it use the fkey to extract the unique securities, the date to
get the newest for all securities, and then hash match?
Basically ITW simply doesn't have enough information for me to make up my
mind. If I could see the queries, how often they occured, and what indexes
they used, then it would be useful. Now, however, I don't trust the numbers.
One last question about the compound constraint. Since the first column in
it is the fkey, should I instead make it an index and drop the fkey index?
What is the difference between a constraint and index in this context anyway
?
I assume that the constraint doesn't do a table search for every insert, so
there's some sort of index that it's using anyway?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7058F853-8697-4DBA-9225-3B544D2E59A4@.microsoft.com...
> I'm learning about indexes and performance, looking at some of my queries
> and
> comparing them with the Index Tuning Wizard (ITW) to figure things out.
> BTW, is there a way to use the Profiler to "get the distinct queries [
on
> table x]"? I would really like to see every query on a table when looking
> at
> the indexes on them, but I can't figure out how to do this.
> Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an
> int
> fkey (security), two floats (one unused), a type, a date and a varchar
> notes
> (typically empty). Data is unique on fkey, type and date. We insert about
> 500
> rows a day in one big batch, so insert performance isn't _too_ much of an
> issue, but because it's a batch you tend to notice it.
> Indexes are currently on the pkey (clustered unique), fkey, type and date.
> There is also a constraint on fkey/type/date.
> My GUESS is that the common types of queries are...
> 1) price for this security and this date
> 2) prices for this date
> 3) prices for this security
> I don't really know which ones are common or not, which is why I was
> asking
> how I could find this out.
> dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
> this makes it an excellent target for an index. In reality there are only
> 5
> values in total, and the VAST majority of the records are a single one.
> Can
> anyone explain this output? Also, why didn't the ITW suggest removing it,
> when it obviously will never be used?
> ITW also suggested removing the index on date. This seems suspect, but I
> can't prove it. The reason it's suspect is that we would have roughtly 500
> prices per date, and about the same number per security. This would seem
> to
> imply that either index should be useful depending on the query. We tend
> to
> GROUP BY on the security, in order to get the newest price per security,
> which would also seem to imply that the date index would be useful -- is
> this
> possible? Can it use the fkey to extract the unique securities, the date
> to
> get the newest for all securities, and then hash match?
> Basically ITW simply doesn't have enough information for me to make up my
> mind. If I could see the queries, how often they occured, and what indexes
> they used, then it would be useful. Now, however, I don't trust the
> numbers.
> One last question about the compound constraint. Since the first column in
> it is the fkey, should I instead make it an index and drop the fkey index?
> What is the difference between a constraint and index in this context
> anyway?
A foreign key constraint will generate a check on insert and update to
guarantee that a related row exists in the primary key table. The
constraint does not store any data, and is no help at all in querying. In
generay you should support each foreign key constraint with an index on the
foreign key table on the foreign key columns.
Now. Your bigger question. What indexing scheme to use. First off since
you only insert 500 rows/day, and you do it in one big batch, you should
probably change your clustered index. You will need to run Profiler to
capture a typical query workload, and this will drive your choice of index.
I suspect that the rignt clustered index is a unique clustered index on
either (security,date,type) or (date,security,type). Which one will depend
on the query workload. Then you will have an index for your primary key,
and clustered index for your alternate key. Once you have that, rerun ITW
and see where you are.
> I assume that the constraint doesn't do a table search for every insert,
> so
> there's some sort of index that it's using anyway?
Yes it uses the primary key index on the PK table, but that doesn't help
your query.
David|||I did respond, quite a bit, to another post you had, but what you are asking
is a pretty generic question, since we do not have specific enough
information here to give detailed repsones; so, about the best you are going
to get is a generic response...but that can be useful too.
1. Relational theory dictates that before an entity is evein 1NF (first
normal form) you MUST specify a Key. Any entity may have one or multiple
candidate keys. There may be a natural key. And, there may even be
complex, compound (composite) keys. One of these has to be the Primary Key.
The rest are alternate keys and should have Unique Key Constraints defined
on them. Oftentimes, the Primary Business Key is a lousy physical key; so,
a surrogate, Auto-Generated, IDENTITY is constructed then chosen as the
Primary Key, physically. This is fine, but keep in mind that that physical
database is just an implementation of a Logical Database Model that supports
a very specific Business Model and that NO surrogate key defines a business
key; thus, the term surrogate. The physical alternate keys, that model the
Business Keys, should always be constrained in order to enforce the logical
business rules.
2. As you go through the normalization process, you wll, hopefully, end up
at 3NF (third normal form), or higher, and a physical consequence of which
is that you will end up with dependent tables that must be constrained with
Foreign Key definitions. In the dependents, these FKCs should be indexed.
In the parent, they must be Uniquely constrained by a PKC or UCs. In the
child, they normally will be reasonably selectible, if not highly
selectible.
3. EVERY TABLE should have a clustered index defined. This is a purely
physical argument and is highly dependent on the SQL Server DBMS. Heaps,
which is what you get when a clustered index is NOT defined, perform
horibly, in terms of storage management and database maitenance. But many
have trouble choosing a proper definition. Here are some generic tips. The
attributes should not be modified but minimally at most. Most often you
would be better served with a single attribute as opposed to a composite
definition. Choose an attribute that is queried often, but by a range
mechanism as opposed to singleton selects (medium cardinality). Then,
finally, the attribute should be as narrow as possible, which is helped by
having single column definitions, but even those, numerics are better than
characters, and narrow characters are better than wider ones. You should
examine the alternate key definitions as possible clustered index candidates
as ALL clustered indexes use UNIQUENESS, either because they are defined as
unique, or because SQL Server appends a GUID to the attribute definition to
make them unique. So, choosing an already uniquely defined key does keep
the overhead down.
4. Now, go through your tables and determine which columns are highly
selectible (high cardinality), that is, if you were to query the table, with
a specific value, what fraction of the table rows would you expect to
return? The fewer, the better candidate for a non-clustered index that
attribute would be. At this stage, I would concentrate solely on single
attribute column indexes. The reason is that statistical histograms are
only stored for the most significant attribute; thus, it is very important
for that attribute to be highly selectible.
The only reason having a composite index would be of value would be becuase
what is known as a "covering" index. A clustered index is a covering index
by definition. This is because a covering index has all the data defining
the index stored on the index page. So, if first name is apart of a name
index, where the last name is the first defining attribute, after the
page(s) is located by a search on last name, SQL Server can also pull the
first name attribute from the index page itself instead of have to run a
bookmark lookup to the referenced data page. Not too many indexes can be
defined this way. A clustered index can because the entire data row is
stored on the clustered index page because, for a clustered index, the index
page IS the data page. Another example would be a non-clustered index but
where every table attribute is used to define it, but that would be highly
redundant. So, besides the clustered index, there are not to many
candidates that could be defined for a covering index.
Moreover, having many singleton indexes provides SQL Server an oportunity to
pull several indexes to perform a set intersection routine, which is far
more efficient than having any one index with composite values.
5. Finally, run sp_createstats against your database. This will create
single-column statistics against every table and every column that does not
already have a supporting index. This can help you determine missed columns
that have high cardinality that could be assisted by creating a
non-clustered index. These statistics also assist the Optimizer to quickly
reject low cardinality columns.
Well, I know this covered quite a bit of gound, some of it not too well, but
there is a ton of literature out there that goes into a lot of detail on
this subject. You need to dig into it. But, a word of caution, many of
these publications are just flat-out wrong. Take everything you read,
your's truely included, with some scepticism. Spend time in the Query
Analyzer. Examine the execution plans. Use hints to override the
Optimizer, not as a production practice, but to see where and how the
Optimizer behaves and why it is far more likely to pick an execution plan
than you are.
Lastly, always make sure your statistics are up to date before you begin any
analysis work. The Optimizer is only as good as these statistics are.
Sincerely,
Anthony Thomas
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7058F853-8697-4DBA-9225-3B544D2E59A4@.microsoft.com...
I'm learning about indexes and performance, looking at some of my queries
and
comparing them with the Index Tuning Wizard (ITW) to figure things out.
BTW, is there a way to use the Profiler to "get the distinct queries [on
table x]"? I would really like to see every query on a table when looking at
the indexes on them, but I can't figure out how to do this.
Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an int
fkey (security), two floats (one unused), a type, a date and a varchar notes
(typically empty). Data is unique on fkey, type and date. We insert about
500
rows a day in one big batch, so insert performance isn't _too_ much of an
issue, but because it's a batch you tend to notice it.
Indexes are currently on the pkey (clustered unique), fkey, type and date.
There is also a constraint on fkey/type/date.
My GUESS is that the common types of queries are...
1) price for this security and this date
2) prices for this date
3) prices for this security
I don't really know which ones are common or not, which is why I was asking
how I could find this out.
dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
this makes it an excellent target for an index. In reality there are only 5
values in total, and the VAST majority of the records are a single one. Can
anyone explain this output? Also, why didn't the ITW suggest removing it,
when it obviously will never be used?
ITW also suggested removing the index on date. This seems suspect, but I
can't prove it. The reason it's suspect is that we would have roughtly 500
prices per date, and about the same number per security. This would seem to
imply that either index should be useful depending on the query. We tend to
GROUP BY on the security, in order to get the newest price per security,
which would also seem to imply that the date index would be useful -- is
this
possible? Can it use the fkey to extract the unique securities, the date to
get the newest for all securities, and then hash match?
Basically ITW simply doesn't have enough information for me to make up my
mind. If I could see the queries, how often they occured, and what indexes
they used, then it would be useful. Now, however, I don't trust the numbers.
One last question about the compound constraint. Since the first column in
it is the fkey, should I instead make it an index and drop the fkey index?
What is the difference between a constraint and index in this context
anyway?
I assume that the constraint doesn't do a table search for every insert, so
there's some sort of index that it's using anyway?
Maury
d
comparing them with the Index Tuning Wizard (ITW) to figure things out.
BTW, is there a way to use the Profiler to "get the distinct queries [on
table x]"? I would really like to see every query on a table when looking at
the indexes on them, but I can't figure out how to do this.
Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an int
fkey (security), two floats (one unused), a type, a date and a varchar notes
(typically empty). Data is unique on fkey, type and date. We insert about 50
0
rows a day in one big batch, so insert performance isn't _too_ much of an
issue, but because it's a batch you tend to notice it.
Indexes are currently on the pkey (clustered unique), fkey, type and date.
There is also a constraint on fkey/type/date.
My GUESS is that the common types of queries are...
1) price for this security and this date
2) prices for this date
3) prices for this security
I don't really know which ones are common or not, which is why I was asking
how I could find this out.
dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
this makes it an excellent target for an index. In reality there are only 5
values in total, and the VAST majority of the records are a single one. Can
anyone explain this output? Also, why didn't the ITW suggest removing it,
when it obviously will never be used?
ITW also suggested removing the index on date. This seems suspect, but I
can't prove it. The reason it's suspect is that we would have roughtly 500
prices per date, and about the same number per security. This would seem to
imply that either index should be useful depending on the query. We tend to
GROUP BY on the security, in order to get the newest price per security,
which would also seem to imply that the date index would be useful -- is thi
s
possible? Can it use the fkey to extract the unique securities, the date to
get the newest for all securities, and then hash match?
Basically ITW simply doesn't have enough information for me to make up my
mind. If I could see the queries, how often they occured, and what indexes
they used, then it would be useful. Now, however, I don't trust the numbers.
One last question about the compound constraint. Since the first column in
it is the fkey, should I instead make it an index and drop the fkey index?
What is the difference between a constraint and index in this context anyway
?
I assume that the constraint doesn't do a table search for every insert, so
there's some sort of index that it's using anyway?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7058F853-8697-4DBA-9225-3B544D2E59A4@.microsoft.com...
> I'm learning about indexes and performance, looking at some of my queries
> and
> comparing them with the Index Tuning Wizard (ITW) to figure things out.
> BTW, is there a way to use the Profiler to "get the distinct queries [
on
> table x]"? I would really like to see every query on a table when looking
> at
> the indexes on them, but I can't figure out how to do this.
> Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an
> int
> fkey (security), two floats (one unused), a type, a date and a varchar
> notes
> (typically empty). Data is unique on fkey, type and date. We insert about
> 500
> rows a day in one big batch, so insert performance isn't _too_ much of an
> issue, but because it's a batch you tend to notice it.
> Indexes are currently on the pkey (clustered unique), fkey, type and date.
> There is also a constraint on fkey/type/date.
> My GUESS is that the common types of queries are...
> 1) price for this security and this date
> 2) prices for this date
> 3) prices for this security
> I don't really know which ones are common or not, which is why I was
> asking
> how I could find this out.
> dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
> this makes it an excellent target for an index. In reality there are only
> 5
> values in total, and the VAST majority of the records are a single one.
> Can
> anyone explain this output? Also, why didn't the ITW suggest removing it,
> when it obviously will never be used?
> ITW also suggested removing the index on date. This seems suspect, but I
> can't prove it. The reason it's suspect is that we would have roughtly 500
> prices per date, and about the same number per security. This would seem
> to
> imply that either index should be useful depending on the query. We tend
> to
> GROUP BY on the security, in order to get the newest price per security,
> which would also seem to imply that the date index would be useful -- is
> this
> possible? Can it use the fkey to extract the unique securities, the date
> to
> get the newest for all securities, and then hash match?
> Basically ITW simply doesn't have enough information for me to make up my
> mind. If I could see the queries, how often they occured, and what indexes
> they used, then it would be useful. Now, however, I don't trust the
> numbers.
> One last question about the compound constraint. Since the first column in
> it is the fkey, should I instead make it an index and drop the fkey index?
> What is the difference between a constraint and index in this context
> anyway?
A foreign key constraint will generate a check on insert and update to
guarantee that a related row exists in the primary key table. The
constraint does not store any data, and is no help at all in querying. In
generay you should support each foreign key constraint with an index on the
foreign key table on the foreign key columns.
Now. Your bigger question. What indexing scheme to use. First off since
you only insert 500 rows/day, and you do it in one big batch, you should
probably change your clustered index. You will need to run Profiler to
capture a typical query workload, and this will drive your choice of index.
I suspect that the rignt clustered index is a unique clustered index on
either (security,date,type) or (date,security,type). Which one will depend
on the query workload. Then you will have an index for your primary key,
and clustered index for your alternate key. Once you have that, rerun ITW
and see where you are.
> I assume that the constraint doesn't do a table search for every insert,
> so
> there's some sort of index that it's using anyway?
Yes it uses the primary key index on the PK table, but that doesn't help
your query.
David|||I did respond, quite a bit, to another post you had, but what you are asking
is a pretty generic question, since we do not have specific enough
information here to give detailed repsones; so, about the best you are going
to get is a generic response...but that can be useful too.
1. Relational theory dictates that before an entity is evein 1NF (first
normal form) you MUST specify a Key. Any entity may have one or multiple
candidate keys. There may be a natural key. And, there may even be
complex, compound (composite) keys. One of these has to be the Primary Key.
The rest are alternate keys and should have Unique Key Constraints defined
on them. Oftentimes, the Primary Business Key is a lousy physical key; so,
a surrogate, Auto-Generated, IDENTITY is constructed then chosen as the
Primary Key, physically. This is fine, but keep in mind that that physical
database is just an implementation of a Logical Database Model that supports
a very specific Business Model and that NO surrogate key defines a business
key; thus, the term surrogate. The physical alternate keys, that model the
Business Keys, should always be constrained in order to enforce the logical
business rules.
2. As you go through the normalization process, you wll, hopefully, end up
at 3NF (third normal form), or higher, and a physical consequence of which
is that you will end up with dependent tables that must be constrained with
Foreign Key definitions. In the dependents, these FKCs should be indexed.
In the parent, they must be Uniquely constrained by a PKC or UCs. In the
child, they normally will be reasonably selectible, if not highly
selectible.
3. EVERY TABLE should have a clustered index defined. This is a purely
physical argument and is highly dependent on the SQL Server DBMS. Heaps,
which is what you get when a clustered index is NOT defined, perform
horibly, in terms of storage management and database maitenance. But many
have trouble choosing a proper definition. Here are some generic tips. The
attributes should not be modified but minimally at most. Most often you
would be better served with a single attribute as opposed to a composite
definition. Choose an attribute that is queried often, but by a range
mechanism as opposed to singleton selects (medium cardinality). Then,
finally, the attribute should be as narrow as possible, which is helped by
having single column definitions, but even those, numerics are better than
characters, and narrow characters are better than wider ones. You should
examine the alternate key definitions as possible clustered index candidates
as ALL clustered indexes use UNIQUENESS, either because they are defined as
unique, or because SQL Server appends a GUID to the attribute definition to
make them unique. So, choosing an already uniquely defined key does keep
the overhead down.
4. Now, go through your tables and determine which columns are highly
selectible (high cardinality), that is, if you were to query the table, with
a specific value, what fraction of the table rows would you expect to
return? The fewer, the better candidate for a non-clustered index that
attribute would be. At this stage, I would concentrate solely on single
attribute column indexes. The reason is that statistical histograms are
only stored for the most significant attribute; thus, it is very important
for that attribute to be highly selectible.
The only reason having a composite index would be of value would be becuase
what is known as a "covering" index. A clustered index is a covering index
by definition. This is because a covering index has all the data defining
the index stored on the index page. So, if first name is apart of a name
index, where the last name is the first defining attribute, after the
page(s) is located by a search on last name, SQL Server can also pull the
first name attribute from the index page itself instead of have to run a
bookmark lookup to the referenced data page. Not too many indexes can be
defined this way. A clustered index can because the entire data row is
stored on the clustered index page because, for a clustered index, the index
page IS the data page. Another example would be a non-clustered index but
where every table attribute is used to define it, but that would be highly
redundant. So, besides the clustered index, there are not to many
candidates that could be defined for a covering index.
Moreover, having many singleton indexes provides SQL Server an oportunity to
pull several indexes to perform a set intersection routine, which is far
more efficient than having any one index with composite values.
5. Finally, run sp_createstats against your database. This will create
single-column statistics against every table and every column that does not
already have a supporting index. This can help you determine missed columns
that have high cardinality that could be assisted by creating a
non-clustered index. These statistics also assist the Optimizer to quickly
reject low cardinality columns.
Well, I know this covered quite a bit of gound, some of it not too well, but
there is a ton of literature out there that goes into a lot of detail on
this subject. You need to dig into it. But, a word of caution, many of
these publications are just flat-out wrong. Take everything you read,
your's truely included, with some scepticism. Spend time in the Query
Analyzer. Examine the execution plans. Use hints to override the
Optimizer, not as a production practice, but to see where and how the
Optimizer behaves and why it is far more likely to pick an execution plan
than you are.
Lastly, always make sure your statistics are up to date before you begin any
analysis work. The Optimizer is only as good as these statistics are.
Sincerely,
Anthony Thomas
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7058F853-8697-4DBA-9225-3B544D2E59A4@.microsoft.com...
I'm learning about indexes and performance, looking at some of my queries
and
comparing them with the Index Tuning Wizard (ITW) to figure things out.
BTW, is there a way to use the Profiler to "get the distinct queries [on
table x]"? I would really like to see every query on a table when looking at
the indexes on them, but I can't figure out how to do this.
Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an int
fkey (security), two floats (one unused), a type, a date and a varchar notes
(typically empty). Data is unique on fkey, type and date. We insert about
500
rows a day in one big batch, so insert performance isn't _too_ much of an
issue, but because it's a batch you tend to notice it.
Indexes are currently on the pkey (clustered unique), fkey, type and date.
There is also a constraint on fkey/type/date.
My GUESS is that the common types of queries are...
1) price for this security and this date
2) prices for this date
3) prices for this security
I don't really know which ones are common or not, which is why I was asking
how I could find this out.
dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
this makes it an excellent target for an index. In reality there are only 5
values in total, and the VAST majority of the records are a single one. Can
anyone explain this output? Also, why didn't the ITW suggest removing it,
when it obviously will never be used?
ITW also suggested removing the index on date. This seems suspect, but I
can't prove it. The reason it's suspect is that we would have roughtly 500
prices per date, and about the same number per security. This would seem to
imply that either index should be useful depending on the query. We tend to
GROUP BY on the security, in order to get the newest price per security,
which would also seem to imply that the date index would be useful -- is
this
possible? Can it use the fkey to extract the unique securities, the date to
get the newest for all securities, and then hash match?
Basically ITW simply doesn't have enough information for me to make up my
mind. If I could see the queries, how often they occured, and what indexes
they used, then it would be useful. Now, however, I don't trust the numbers.
One last question about the compound constraint. Since the first column in
it is the fkey, should I instead make it an index and drop the fkey index?
What is the difference between a constraint and index in this context
anyway?
I assume that the constraint doesn't do a table search for every insert, so
there's some sort of index that it's using anyway?
Maury
More index questions
I'm learning about indexes and performance, looking at some of my queries and
comparing them with the Index Tuning Wizard (ITW) to figure things out.
BTW, is there a way to use the Profiler to "get the distinct queries [on
table x]"? I would really like to see every query on a table when looking at
the indexes on them, but I can't figure out how to do this.
Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an int
fkey (security), two floats (one unused), a type, a date and a varchar notes
(typically empty). Data is unique on fkey, type and date. We insert about 500
rows a day in one big batch, so insert performance isn't _too_ much of an
issue, but because it's a batch you tend to notice it.
Indexes are currently on the pkey (clustered unique), fkey, type and date.
There is also a constraint on fkey/type/date.
My GUESS is that the common types of queries are...
1) price for this security and this date
2) prices for this date
3) prices for this security
I don't really know which ones are common or not, which is why I was asking
how I could find this out.
dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
this makes it an excellent target for an index. In reality there are only 5
values in total, and the VAST majority of the records are a single one. Can
anyone explain this output? Also, why didn't the ITW suggest removing it,
when it obviously will never be used?
ITW also suggested removing the index on date. This seems suspect, but I
can't prove it. The reason it's suspect is that we would have roughtly 500
prices per date, and about the same number per security. This would seem to
imply that either index should be useful depending on the query. We tend to
GROUP BY on the security, in order to get the newest price per security,
which would also seem to imply that the date index would be useful -- is this
possible? Can it use the fkey to extract the unique securities, the date to
get the newest for all securities, and then hash match?
Basically ITW simply doesn't have enough information for me to make up my
mind. If I could see the queries, how often they occured, and what indexes
they used, then it would be useful. Now, however, I don't trust the numbers.
One last question about the compound constraint. Since the first column in
it is the fkey, should I instead make it an index and drop the fkey index?
What is the difference between a constraint and index in this context anyway?
I assume that the constraint doesn't do a table search for every insert, so
there's some sort of index that it's using anyway?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7058F853-8697-4DBA-9225-3B544D2E59A4@.microsoft.com...
> I'm learning about indexes and performance, looking at some of my queries
> and
> comparing them with the Index Tuning Wizard (ITW) to figure things out.
> BTW, is there a way to use the Profiler to "get the distinct queries [on
> table x]"? I would really like to see every query on a table when looking
> at
> the indexes on them, but I can't figure out how to do this.
> Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an
> int
> fkey (security), two floats (one unused), a type, a date and a varchar
> notes
> (typically empty). Data is unique on fkey, type and date. We insert about
> 500
> rows a day in one big batch, so insert performance isn't _too_ much of an
> issue, but because it's a batch you tend to notice it.
> Indexes are currently on the pkey (clustered unique), fkey, type and date.
> There is also a constraint on fkey/type/date.
> My GUESS is that the common types of queries are...
> 1) price for this security and this date
> 2) prices for this date
> 3) prices for this security
> I don't really know which ones are common or not, which is why I was
> asking
> how I could find this out.
> dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
> this makes it an excellent target for an index. In reality there are only
> 5
> values in total, and the VAST majority of the records are a single one.
> Can
> anyone explain this output? Also, why didn't the ITW suggest removing it,
> when it obviously will never be used?
> ITW also suggested removing the index on date. This seems suspect, but I
> can't prove it. The reason it's suspect is that we would have roughtly 500
> prices per date, and about the same number per security. This would seem
> to
> imply that either index should be useful depending on the query. We tend
> to
> GROUP BY on the security, in order to get the newest price per security,
> which would also seem to imply that the date index would be useful -- is
> this
> possible? Can it use the fkey to extract the unique securities, the date
> to
> get the newest for all securities, and then hash match?
> Basically ITW simply doesn't have enough information for me to make up my
> mind. If I could see the queries, how often they occured, and what indexes
> they used, then it would be useful. Now, however, I don't trust the
> numbers.
> One last question about the compound constraint. Since the first column in
> it is the fkey, should I instead make it an index and drop the fkey index?
> What is the difference between a constraint and index in this context
> anyway?
A foreign key constraint will generate a check on insert and update to
guarantee that a related row exists in the primary key table. The
constraint does not store any data, and is no help at all in querying. In
generay you should support each foreign key constraint with an index on the
foreign key table on the foreign key columns.
Now. Your bigger question. What indexing scheme to use. First off since
you only insert 500 rows/day, and you do it in one big batch, you should
probably change your clustered index. You will need to run Profiler to
capture a typical query workload, and this will drive your choice of index.
I suspect that the rignt clustered index is a unique clustered index on
either (security,date,type) or (date,security,type). Which one will depend
on the query workload. Then you will have an index for your primary key,
and clustered index for your alternate key. Once you have that, rerun ITW
and see where you are.
> I assume that the constraint doesn't do a table search for every insert,
> so
> there's some sort of index that it's using anyway?
Yes it uses the primary key index on the PK table, but that doesn't help
your query.
David|||I did respond, quite a bit, to another post you had, but what you are asking
is a pretty generic question, since we do not have specific enough
information here to give detailed repsones; so, about the best you are going
to get is a generic response...but that can be useful too.
1. Relational theory dictates that before an entity is evein 1NF (first
normal form) you MUST specify a Key. Any entity may have one or multiple
candidate keys. There may be a natural key. And, there may even be
complex, compound (composite) keys. One of these has to be the Primary Key.
The rest are alternate keys and should have Unique Key Constraints defined
on them. Oftentimes, the Primary Business Key is a lousy physical key; so,
a surrogate, Auto-Generated, IDENTITY is constructed then chosen as the
Primary Key, physically. This is fine, but keep in mind that that physical
database is just an implementation of a Logical Database Model that supports
a very specific Business Model and that NO surrogate key defines a business
key; thus, the term surrogate. The physical alternate keys, that model the
Business Keys, should always be constrained in order to enforce the logical
business rules.
2. As you go through the normalization process, you wll, hopefully, end up
at 3NF (third normal form), or higher, and a physical consequence of which
is that you will end up with dependent tables that must be constrained with
Foreign Key definitions. In the dependents, these FKCs should be indexed.
In the parent, they must be Uniquely constrained by a PKC or UCs. In the
child, they normally will be reasonably selectible, if not highly
selectible.
3. EVERY TABLE should have a clustered index defined. This is a purely
physical argument and is highly dependent on the SQL Server DBMS. Heaps,
which is what you get when a clustered index is NOT defined, perform
horibly, in terms of storage management and database maitenance. But many
have trouble choosing a proper definition. Here are some generic tips. The
attributes should not be modified but minimally at most. Most often you
would be better served with a single attribute as opposed to a composite
definition. Choose an attribute that is queried often, but by a range
mechanism as opposed to singleton selects (medium cardinality). Then,
finally, the attribute should be as narrow as possible, which is helped by
having single column definitions, but even those, numerics are better than
characters, and narrow characters are better than wider ones. You should
examine the alternate key definitions as possible clustered index candidates
as ALL clustered indexes use UNIQUENESS, either because they are defined as
unique, or because SQL Server appends a GUID to the attribute definition to
make them unique. So, choosing an already uniquely defined key does keep
the overhead down.
4. Now, go through your tables and determine which columns are highly
selectible (high cardinality), that is, if you were to query the table, with
a specific value, what fraction of the table rows would you expect to
return? The fewer, the better candidate for a non-clustered index that
attribute would be. At this stage, I would concentrate solely on single
attribute column indexes. The reason is that statistical histograms are
only stored for the most significant attribute; thus, it is very important
for that attribute to be highly selectible.
The only reason having a composite index would be of value would be becuase
what is known as a "covering" index. A clustered index is a covering index
by definition. This is because a covering index has all the data defining
the index stored on the index page. So, if first name is apart of a name
index, where the last name is the first defining attribute, after the
page(s) is located by a search on last name, SQL Server can also pull the
first name attribute from the index page itself instead of have to run a
bookmark lookup to the referenced data page. Not too many indexes can be
defined this way. A clustered index can because the entire data row is
stored on the clustered index page because, for a clustered index, the index
page IS the data page. Another example would be a non-clustered index but
where every table attribute is used to define it, but that would be highly
redundant. So, besides the clustered index, there are not to many
candidates that could be defined for a covering index.
Moreover, having many singleton indexes provides SQL Server an oportunity to
pull several indexes to perform a set intersection routine, which is far
more efficient than having any one index with composite values.
5. Finally, run sp_createstats against your database. This will create
single-column statistics against every table and every column that does not
already have a supporting index. This can help you determine missed columns
that have high cardinality that could be assisted by creating a
non-clustered index. These statistics also assist the Optimizer to quickly
reject low cardinality columns.
Well, I know this covered quite a bit of gound, some of it not too well, but
there is a ton of literature out there that goes into a lot of detail on
this subject. You need to dig into it. But, a word of caution, many of
these publications are just flat-out wrong. Take everything you read,
your's truely included, with some scepticism. Spend time in the Query
Analyzer. Examine the execution plans. Use hints to override the
Optimizer, not as a production practice, but to see where and how the
Optimizer behaves and why it is far more likely to pick an execution plan
than you are.
Lastly, always make sure your statistics are up to date before you begin any
analysis work. The Optimizer is only as good as these statistics are.
Sincerely,
Anthony Thomas
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7058F853-8697-4DBA-9225-3B544D2E59A4@.microsoft.com...
I'm learning about indexes and performance, looking at some of my queries
and
comparing them with the Index Tuning Wizard (ITW) to figure things out.
BTW, is there a way to use the Profiler to "get the distinct queries [on
table x]"? I would really like to see every query on a table when looking at
the indexes on them, but I can't figure out how to do this.
Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an int
fkey (security), two floats (one unused), a type, a date and a varchar notes
(typically empty). Data is unique on fkey, type and date. We insert about
500
rows a day in one big batch, so insert performance isn't _too_ much of an
issue, but because it's a batch you tend to notice it.
Indexes are currently on the pkey (clustered unique), fkey, type and date.
There is also a constraint on fkey/type/date.
My GUESS is that the common types of queries are...
1) price for this security and this date
2) prices for this date
3) prices for this security
I don't really know which ones are common or not, which is why I was asking
how I could find this out.
dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
this makes it an excellent target for an index. In reality there are only 5
values in total, and the VAST majority of the records are a single one. Can
anyone explain this output? Also, why didn't the ITW suggest removing it,
when it obviously will never be used?
ITW also suggested removing the index on date. This seems suspect, but I
can't prove it. The reason it's suspect is that we would have roughtly 500
prices per date, and about the same number per security. This would seem to
imply that either index should be useful depending on the query. We tend to
GROUP BY on the security, in order to get the newest price per security,
which would also seem to imply that the date index would be useful -- is
this
possible? Can it use the fkey to extract the unique securities, the date to
get the newest for all securities, and then hash match?
Basically ITW simply doesn't have enough information for me to make up my
mind. If I could see the queries, how often they occured, and what indexes
they used, then it would be useful. Now, however, I don't trust the numbers.
One last question about the compound constraint. Since the first column in
it is the fkey, should I instead make it an index and drop the fkey index?
What is the difference between a constraint and index in this context
anyway?
I assume that the constraint doesn't do a table search for every insert, so
there's some sort of index that it's using anyway?
Maury
comparing them with the Index Tuning Wizard (ITW) to figure things out.
BTW, is there a way to use the Profiler to "get the distinct queries [on
table x]"? I would really like to see every query on a table when looking at
the indexes on them, but I can't figure out how to do this.
Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an int
fkey (security), two floats (one unused), a type, a date and a varchar notes
(typically empty). Data is unique on fkey, type and date. We insert about 500
rows a day in one big batch, so insert performance isn't _too_ much of an
issue, but because it's a batch you tend to notice it.
Indexes are currently on the pkey (clustered unique), fkey, type and date.
There is also a constraint on fkey/type/date.
My GUESS is that the common types of queries are...
1) price for this security and this date
2) prices for this date
3) prices for this security
I don't really know which ones are common or not, which is why I was asking
how I could find this out.
dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
this makes it an excellent target for an index. In reality there are only 5
values in total, and the VAST majority of the records are a single one. Can
anyone explain this output? Also, why didn't the ITW suggest removing it,
when it obviously will never be used?
ITW also suggested removing the index on date. This seems suspect, but I
can't prove it. The reason it's suspect is that we would have roughtly 500
prices per date, and about the same number per security. This would seem to
imply that either index should be useful depending on the query. We tend to
GROUP BY on the security, in order to get the newest price per security,
which would also seem to imply that the date index would be useful -- is this
possible? Can it use the fkey to extract the unique securities, the date to
get the newest for all securities, and then hash match?
Basically ITW simply doesn't have enough information for me to make up my
mind. If I could see the queries, how often they occured, and what indexes
they used, then it would be useful. Now, however, I don't trust the numbers.
One last question about the compound constraint. Since the first column in
it is the fkey, should I instead make it an index and drop the fkey index?
What is the difference between a constraint and index in this context anyway?
I assume that the constraint doesn't do a table search for every insert, so
there's some sort of index that it's using anyway?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7058F853-8697-4DBA-9225-3B544D2E59A4@.microsoft.com...
> I'm learning about indexes and performance, looking at some of my queries
> and
> comparing them with the Index Tuning Wizard (ITW) to figure things out.
> BTW, is there a way to use the Profiler to "get the distinct queries [on
> table x]"? I would really like to see every query on a table when looking
> at
> the indexes on them, but I can't figure out how to do this.
> Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an
> int
> fkey (security), two floats (one unused), a type, a date and a varchar
> notes
> (typically empty). Data is unique on fkey, type and date. We insert about
> 500
> rows a day in one big batch, so insert performance isn't _too_ much of an
> issue, but because it's a batch you tend to notice it.
> Indexes are currently on the pkey (clustered unique), fkey, type and date.
> There is also a constraint on fkey/type/date.
> My GUESS is that the common types of queries are...
> 1) price for this security and this date
> 2) prices for this date
> 3) prices for this security
> I don't really know which ones are common or not, which is why I was
> asking
> how I could find this out.
> dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
> this makes it an excellent target for an index. In reality there are only
> 5
> values in total, and the VAST majority of the records are a single one.
> Can
> anyone explain this output? Also, why didn't the ITW suggest removing it,
> when it obviously will never be used?
> ITW also suggested removing the index on date. This seems suspect, but I
> can't prove it. The reason it's suspect is that we would have roughtly 500
> prices per date, and about the same number per security. This would seem
> to
> imply that either index should be useful depending on the query. We tend
> to
> GROUP BY on the security, in order to get the newest price per security,
> which would also seem to imply that the date index would be useful -- is
> this
> possible? Can it use the fkey to extract the unique securities, the date
> to
> get the newest for all securities, and then hash match?
> Basically ITW simply doesn't have enough information for me to make up my
> mind. If I could see the queries, how often they occured, and what indexes
> they used, then it would be useful. Now, however, I don't trust the
> numbers.
> One last question about the compound constraint. Since the first column in
> it is the fkey, should I instead make it an index and drop the fkey index?
> What is the difference between a constraint and index in this context
> anyway?
A foreign key constraint will generate a check on insert and update to
guarantee that a related row exists in the primary key table. The
constraint does not store any data, and is no help at all in querying. In
generay you should support each foreign key constraint with an index on the
foreign key table on the foreign key columns.
Now. Your bigger question. What indexing scheme to use. First off since
you only insert 500 rows/day, and you do it in one big batch, you should
probably change your clustered index. You will need to run Profiler to
capture a typical query workload, and this will drive your choice of index.
I suspect that the rignt clustered index is a unique clustered index on
either (security,date,type) or (date,security,type). Which one will depend
on the query workload. Then you will have an index for your primary key,
and clustered index for your alternate key. Once you have that, rerun ITW
and see where you are.
> I assume that the constraint doesn't do a table search for every insert,
> so
> there's some sort of index that it's using anyway?
Yes it uses the primary key index on the PK table, but that doesn't help
your query.
David|||I did respond, quite a bit, to another post you had, but what you are asking
is a pretty generic question, since we do not have specific enough
information here to give detailed repsones; so, about the best you are going
to get is a generic response...but that can be useful too.
1. Relational theory dictates that before an entity is evein 1NF (first
normal form) you MUST specify a Key. Any entity may have one or multiple
candidate keys. There may be a natural key. And, there may even be
complex, compound (composite) keys. One of these has to be the Primary Key.
The rest are alternate keys and should have Unique Key Constraints defined
on them. Oftentimes, the Primary Business Key is a lousy physical key; so,
a surrogate, Auto-Generated, IDENTITY is constructed then chosen as the
Primary Key, physically. This is fine, but keep in mind that that physical
database is just an implementation of a Logical Database Model that supports
a very specific Business Model and that NO surrogate key defines a business
key; thus, the term surrogate. The physical alternate keys, that model the
Business Keys, should always be constrained in order to enforce the logical
business rules.
2. As you go through the normalization process, you wll, hopefully, end up
at 3NF (third normal form), or higher, and a physical consequence of which
is that you will end up with dependent tables that must be constrained with
Foreign Key definitions. In the dependents, these FKCs should be indexed.
In the parent, they must be Uniquely constrained by a PKC or UCs. In the
child, they normally will be reasonably selectible, if not highly
selectible.
3. EVERY TABLE should have a clustered index defined. This is a purely
physical argument and is highly dependent on the SQL Server DBMS. Heaps,
which is what you get when a clustered index is NOT defined, perform
horibly, in terms of storage management and database maitenance. But many
have trouble choosing a proper definition. Here are some generic tips. The
attributes should not be modified but minimally at most. Most often you
would be better served with a single attribute as opposed to a composite
definition. Choose an attribute that is queried often, but by a range
mechanism as opposed to singleton selects (medium cardinality). Then,
finally, the attribute should be as narrow as possible, which is helped by
having single column definitions, but even those, numerics are better than
characters, and narrow characters are better than wider ones. You should
examine the alternate key definitions as possible clustered index candidates
as ALL clustered indexes use UNIQUENESS, either because they are defined as
unique, or because SQL Server appends a GUID to the attribute definition to
make them unique. So, choosing an already uniquely defined key does keep
the overhead down.
4. Now, go through your tables and determine which columns are highly
selectible (high cardinality), that is, if you were to query the table, with
a specific value, what fraction of the table rows would you expect to
return? The fewer, the better candidate for a non-clustered index that
attribute would be. At this stage, I would concentrate solely on single
attribute column indexes. The reason is that statistical histograms are
only stored for the most significant attribute; thus, it is very important
for that attribute to be highly selectible.
The only reason having a composite index would be of value would be becuase
what is known as a "covering" index. A clustered index is a covering index
by definition. This is because a covering index has all the data defining
the index stored on the index page. So, if first name is apart of a name
index, where the last name is the first defining attribute, after the
page(s) is located by a search on last name, SQL Server can also pull the
first name attribute from the index page itself instead of have to run a
bookmark lookup to the referenced data page. Not too many indexes can be
defined this way. A clustered index can because the entire data row is
stored on the clustered index page because, for a clustered index, the index
page IS the data page. Another example would be a non-clustered index but
where every table attribute is used to define it, but that would be highly
redundant. So, besides the clustered index, there are not to many
candidates that could be defined for a covering index.
Moreover, having many singleton indexes provides SQL Server an oportunity to
pull several indexes to perform a set intersection routine, which is far
more efficient than having any one index with composite values.
5. Finally, run sp_createstats against your database. This will create
single-column statistics against every table and every column that does not
already have a supporting index. This can help you determine missed columns
that have high cardinality that could be assisted by creating a
non-clustered index. These statistics also assist the Optimizer to quickly
reject low cardinality columns.
Well, I know this covered quite a bit of gound, some of it not too well, but
there is a ton of literature out there that goes into a lot of detail on
this subject. You need to dig into it. But, a word of caution, many of
these publications are just flat-out wrong. Take everything you read,
your's truely included, with some scepticism. Spend time in the Query
Analyzer. Examine the execution plans. Use hints to override the
Optimizer, not as a production practice, but to see where and how the
Optimizer behaves and why it is far more likely to pick an execution plan
than you are.
Lastly, always make sure your statistics are up to date before you begin any
analysis work. The Optimizer is only as good as these statistics are.
Sincerely,
Anthony Thomas
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7058F853-8697-4DBA-9225-3B544D2E59A4@.microsoft.com...
I'm learning about indexes and performance, looking at some of my queries
and
comparing them with the Index Tuning Wizard (ITW) to figure things out.
BTW, is there a way to use the Profiler to "get the distinct queries [on
table x]"? I would really like to see every query on a table when looking at
the indexes on them, but I can't figure out how to do this.
Ok, so right now I'm looking at tblPrices, ~500,000 rows with a pkey, an int
fkey (security), two floats (one unused), a type, a date and a varchar notes
(typically empty). Data is unique on fkey, type and date. We insert about
500
rows a day in one big batch, so insert performance isn't _too_ much of an
issue, but because it's a batch you tend to notice it.
Indexes are currently on the pkey (clustered unique), fkey, type and date.
There is also a constraint on fkey/type/date.
My GUESS is that the common types of queries are...
1) price for this security and this date
2) prices for this date
3) prices for this security
I don't really know which ones are common or not, which is why I was asking
how I could find this out.
dbcc SHOW_STATISTICS (tblprices, type) said "0.0". According to the docs
this makes it an excellent target for an index. In reality there are only 5
values in total, and the VAST majority of the records are a single one. Can
anyone explain this output? Also, why didn't the ITW suggest removing it,
when it obviously will never be used?
ITW also suggested removing the index on date. This seems suspect, but I
can't prove it. The reason it's suspect is that we would have roughtly 500
prices per date, and about the same number per security. This would seem to
imply that either index should be useful depending on the query. We tend to
GROUP BY on the security, in order to get the newest price per security,
which would also seem to imply that the date index would be useful -- is
this
possible? Can it use the fkey to extract the unique securities, the date to
get the newest for all securities, and then hash match?
Basically ITW simply doesn't have enough information for me to make up my
mind. If I could see the queries, how often they occured, and what indexes
they used, then it would be useful. Now, however, I don't trust the numbers.
One last question about the compound constraint. Since the first column in
it is the fkey, should I instead make it an index and drop the fkey index?
What is the difference between a constraint and index in this context
anyway?
I assume that the constraint doesn't do a table search for every insert, so
there's some sort of index that it's using anyway?
Maury
Wednesday, March 21, 2012
Monitoring/spying on the CE execution engine
Evening all,
I'm trying to do some profiling of a mobile application to determine where our performance bottleneck is. We have some conflicting information suggesting that inefficient usage of SqlCE might be the cause - but that code exists in a black-box library so we can't see what it's doing.
Are there any tools or configuration options to get the SqlCE execution engine to reveal what connections/queries it's being asked to perform? A simple list with some timestamps would be sufficient - just so we can map from our high-level data...
Any thoughts would be appreciated!
Jack
There is not a Profiler like the one you may use in SQL Server, however you can explore the execution plan that SQL CE's query procesor will use on any given query. Here's how: Connect to your SQL CE database from SQL Server 2005 Management Studio. Open a new query and then from the Query menu choose 'Display Estimated Execution Plan'.
Darren
sql
Monitoring Wish List ...
Bob,
I'd take a look at the Microsoft SQL Server 2000
operations guide - as it has reccommendations for creating
a performance baseline..
Take a look at :-
http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/maintain/operate/opsguide/sqlo
ps5.asp
and look for 'Creating The Baseline - Suggested Counters'
Hope this helps.
production. One of
appropriate performance
eight processors. 8
log files to a
Customers access the
expectation is to
months, partly
existing customers
databases. The obvious
customers are added. The
with no load on the
that we can project
standard procedure
such monitoring on
currently going to look at
Unfortunately, the info at the link you provided is pretty generic. I guess
I'm looking for specifics based on the experiences of the experts that post
here.
"Steve Hindmarsh" <steve@.nowhere.com> wrote in message
news:019f01c3db96$3e331ba0$a401280a@.phx.gbl...[QUOTE]
> Bob,
> I'd take a look at the Microsoft SQL Server 2000
> operations guide - as it has reccommendations for creating
> a performance baseline..
> Take a look at :-
> http://www.microsoft.com/technet/treeview/default.asp?
> url=/technet/prodtechnol/sql/maintain/operate/opsguide/sqlo
> ps5.asp
> and look for 'Creating The Baseline - Suggested Counters'
> Hope this helps.
>
> production. One of
> appropriate performance
> eight processors. 8
> log files to a
> Customers access the
> expectation is to
> months, partly
> existing customers
> databases. The obvious
> customers are added. The
> with no load on the
> that we can project
> standard procedure
> such monitoring on
> currently going to look at
I'd take a look at the Microsoft SQL Server 2000
operations guide - as it has reccommendations for creating
a performance baseline..
Take a look at :-
http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/maintain/operate/opsguide/sqlo
ps5.asp
and look for 'Creating The Baseline - Suggested Counters'
Hope this helps.
quote:
>--Original Message--
>We are in the process of bringing a new SQL Cluster into
production. One of
quote:
>the things we are insisting on is that we have
appropriate performance
quote:
>monitoring in place BEFORE our customers start using it.
>Briefly, the cluster is twin quad xeons, expandable to
eight processors. 8
quote:
>gig ram expandable to 64. Fiber channel to a drive array,
log files to a
quote:
>separate array. The cluster is an ASP environment.
Customers access the
quote:
>database server through a load balanced Citrix farm. The
expectation is to
quote:
>go from about 100 customers to over 1000 in the next 24
months, partly
quote:
>through aquiring new customers and partly through moving
existing customers
quote:
>to the ASP model. Each customer has a separate set of
databases. The obvious
quote:
>problem is that performance is maintianed as new
customers are added. The
quote:
>monitoring plan is to take a baseline set of readings
with no load on the
quote:
>new cluster, then take data at prescribed intervals so
that we can project
quote:
>utilization and adjust planned upgrades accordingly. All
standard procedure
quote:
>(or at least it should be, right?).
>My question is, that if you had the opportunity to set up
such monitoring on
quote:
>a pristine system, what would you include? We are
currently going to look at
quote:Thanks Steve.
>the following:
>Logical Disk:
> Avg Queue Length
> Current Disk Queue
>Physical Disk
> Avg Queue Length
> Current Disk Queue
>Memory
> Pages/sec
> Page Reads/sec
> Page Faults/sec
>System
> Processor Queue Length
> CPU %
>SQL:
> Cache Hit Ratio
> I/O - Single Page Write/Sec
> I/O - Page Reads/Sec
> I/O - Transactions/Sec
> User Connections
>
>What would you add or remove from this list?
>
>Thanks,
>Bob Castleman
>SuccessWare SoftWare
>
>.
>
Unfortunately, the info at the link you provided is pretty generic. I guess
I'm looking for specifics based on the experiences of the experts that post
here.
"Steve Hindmarsh" <steve@.nowhere.com> wrote in message
news:019f01c3db96$3e331ba0$a401280a@.phx.gbl...[QUOTE]
> Bob,
> I'd take a look at the Microsoft SQL Server 2000
> operations guide - as it has reccommendations for creating
> a performance baseline..
> Take a look at :-
> http://www.microsoft.com/technet/treeview/default.asp?
> url=/technet/prodtechnol/sql/maintain/operate/opsguide/sqlo
> ps5.asp
> and look for 'Creating The Baseline - Suggested Counters'
> Hope this helps.
>
> production. One of
> appropriate performance
> eight processors. 8
> log files to a
> Customers access the
> expectation is to
> months, partly
> existing customers
> databases. The obvious
> customers are added. The
> with no load on the
> that we can project
> standard procedure
> such monitoring on
> currently going to look at
Monitoring Wish List ...
We are in the process of bringing a new SQL Cluster into production. One of
the things we are insisting on is that we have appropriate performance
monitoring in place BEFORE our customers start using it.
Briefly, the cluster is twin quad xeons, expandable to eight processors. 8
gig ram expandable to 64. Fiber channel to a drive array, log files to a
separate array. The cluster is an ASP environment. Customers access the
database server through a load balanced Citrix farm. The expectation is to
go from about 100 customers to over 1000 in the next 24 months, partly
through aquiring new customers and partly through moving existing customers
to the ASP model. Each customer has a separate set of databases. The obvious
problem is that performance is maintianed as new customers are added. The
monitoring plan is to take a baseline set of readings with no load on the
new cluster, then take data at prescribed intervals so that we can project
utilization and adjust planned upgrades accordingly. All standard procedure
(or at least it should be, right?).
My question is, that if you had the opportunity to set up such monitoring on
a pristine system, what would you include? We are currently going to look at
the following:
Logical Disk:
Avg Queue Length
Current Disk Queue
Physical Disk
Avg Queue Length
Current Disk Queue
Memory
Pages/sec
Page Reads/sec
Page Faults/sec
System
Processor Queue Length
CPU %
SQL:
Cache Hit Ratio
I/O - Single Page Write/Sec
I/O - Page Reads/Sec
I/O - Transactions/Sec
User Connections
What would you add or remove from this list?
Thanks,
Bob Castleman
SuccessWare SoftWareBob,
I'd take a look at the Microsoft SQL Server 2000
operations guide - as it has reccommendations for creating
a performance baseline..
Take a look at :-
http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/maintain/operate/opsguide/sqlo
ps5.asp
and look for 'Creating The Baseline - Suggested Counters'
Hope this helps.
>--Original Message--
>We are in the process of bringing a new SQL Cluster into
production. One of
>the things we are insisting on is that we have
appropriate performance
>monitoring in place BEFORE our customers start using it.
>Briefly, the cluster is twin quad xeons, expandable to
eight processors. 8
>gig ram expandable to 64. Fiber channel to a drive array,
log files to a
>separate array. The cluster is an ASP environment.
Customers access the
>database server through a load balanced Citrix farm. The
expectation is to
>go from about 100 customers to over 1000 in the next 24
months, partly
>through aquiring new customers and partly through moving
existing customers
>to the ASP model. Each customer has a separate set of
databases. The obvious
>problem is that performance is maintianed as new
customers are added. The
>monitoring plan is to take a baseline set of readings
with no load on the
>new cluster, then take data at prescribed intervals so
that we can project
>utilization and adjust planned upgrades accordingly. All
standard procedure
>(or at least it should be, right?).
>My question is, that if you had the opportunity to set up
such monitoring on
>a pristine system, what would you include? We are
currently going to look at
>the following:
>Logical Disk:
> Avg Queue Length
> Current Disk Queue
>Physical Disk
> Avg Queue Length
> Current Disk Queue
>Memory
> Pages/sec
> Page Reads/sec
> Page Faults/sec
>System
> Processor Queue Length
> CPU %
>SQL:
> Cache Hit Ratio
> I/O - Single Page Write/Sec
> I/O - Page Reads/Sec
> I/O - Transactions/Sec
> User Connections
>
>What would you add or remove from this list?
>
>Thanks,
>Bob Castleman
>SuccessWare SoftWare
>
>.
>|||Thanks Steve.
Unfortunately, the info at the link you provided is pretty generic. I guess
I'm looking for specifics based on the experiences of the experts that post
here.
"Steve Hindmarsh" <steve@.nowhere.com> wrote in message
news:019f01c3db96$3e331ba0$a401280a@.phx.gbl...
> Bob,
> I'd take a look at the Microsoft SQL Server 2000
> operations guide - as it has reccommendations for creating
> a performance baseline..
> Take a look at :-
> http://www.microsoft.com/technet/treeview/default.asp?
> url=/technet/prodtechnol/sql/maintain/operate/opsguide/sqlo
> ps5.asp
> and look for 'Creating The Baseline - Suggested Counters'
> Hope this helps.
> >--Original Message--
> >We are in the process of bringing a new SQL Cluster into
> production. One of
> >the things we are insisting on is that we have
> appropriate performance
> >monitoring in place BEFORE our customers start using it.
> >
> >Briefly, the cluster is twin quad xeons, expandable to
> eight processors. 8
> >gig ram expandable to 64. Fiber channel to a drive array,
> log files to a
> >separate array. The cluster is an ASP environment.
> Customers access the
> >database server through a load balanced Citrix farm. The
> expectation is to
> >go from about 100 customers to over 1000 in the next 24
> months, partly
> >through aquiring new customers and partly through moving
> existing customers
> >to the ASP model. Each customer has a separate set of
> databases. The obvious
> >problem is that performance is maintianed as new
> customers are added. The
> >monitoring plan is to take a baseline set of readings
> with no load on the
> >new cluster, then take data at prescribed intervals so
> that we can project
> >utilization and adjust planned upgrades accordingly. All
> standard procedure
> >(or at least it should be, right?).
> >
> >My question is, that if you had the opportunity to set up
> such monitoring on
> >a pristine system, what would you include? We are
> currently going to look at
> >the following:
> >
> >Logical Disk:
> > Avg Queue Length
> > Current Disk Queue
> >Physical Disk
> > Avg Queue Length
> > Current Disk Queue
> >Memory
> > Pages/sec
> > Page Reads/sec
> > Page Faults/sec
> >System
> > Processor Queue Length
> > CPU %
> >SQL:
> > Cache Hit Ratio
> > I/O - Single Page Write/Sec
> > I/O - Page Reads/Sec
> > I/O - Transactions/Sec
> > User Connections
> >
> >
> >
> >What would you add or remove from this list?
> >
> >
> >Thanks,
> >
> >Bob Castleman
> >SuccessWare SoftWare
> >
> >
> >.
> >
the things we are insisting on is that we have appropriate performance
monitoring in place BEFORE our customers start using it.
Briefly, the cluster is twin quad xeons, expandable to eight processors. 8
gig ram expandable to 64. Fiber channel to a drive array, log files to a
separate array. The cluster is an ASP environment. Customers access the
database server through a load balanced Citrix farm. The expectation is to
go from about 100 customers to over 1000 in the next 24 months, partly
through aquiring new customers and partly through moving existing customers
to the ASP model. Each customer has a separate set of databases. The obvious
problem is that performance is maintianed as new customers are added. The
monitoring plan is to take a baseline set of readings with no load on the
new cluster, then take data at prescribed intervals so that we can project
utilization and adjust planned upgrades accordingly. All standard procedure
(or at least it should be, right?).
My question is, that if you had the opportunity to set up such monitoring on
a pristine system, what would you include? We are currently going to look at
the following:
Logical Disk:
Avg Queue Length
Current Disk Queue
Physical Disk
Avg Queue Length
Current Disk Queue
Memory
Pages/sec
Page Reads/sec
Page Faults/sec
System
Processor Queue Length
CPU %
SQL:
Cache Hit Ratio
I/O - Single Page Write/Sec
I/O - Page Reads/Sec
I/O - Transactions/Sec
User Connections
What would you add or remove from this list?
Thanks,
Bob Castleman
SuccessWare SoftWareBob,
I'd take a look at the Microsoft SQL Server 2000
operations guide - as it has reccommendations for creating
a performance baseline..
Take a look at :-
http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/maintain/operate/opsguide/sqlo
ps5.asp
and look for 'Creating The Baseline - Suggested Counters'
Hope this helps.
>--Original Message--
>We are in the process of bringing a new SQL Cluster into
production. One of
>the things we are insisting on is that we have
appropriate performance
>monitoring in place BEFORE our customers start using it.
>Briefly, the cluster is twin quad xeons, expandable to
eight processors. 8
>gig ram expandable to 64. Fiber channel to a drive array,
log files to a
>separate array. The cluster is an ASP environment.
Customers access the
>database server through a load balanced Citrix farm. The
expectation is to
>go from about 100 customers to over 1000 in the next 24
months, partly
>through aquiring new customers and partly through moving
existing customers
>to the ASP model. Each customer has a separate set of
databases. The obvious
>problem is that performance is maintianed as new
customers are added. The
>monitoring plan is to take a baseline set of readings
with no load on the
>new cluster, then take data at prescribed intervals so
that we can project
>utilization and adjust planned upgrades accordingly. All
standard procedure
>(or at least it should be, right?).
>My question is, that if you had the opportunity to set up
such monitoring on
>a pristine system, what would you include? We are
currently going to look at
>the following:
>Logical Disk:
> Avg Queue Length
> Current Disk Queue
>Physical Disk
> Avg Queue Length
> Current Disk Queue
>Memory
> Pages/sec
> Page Reads/sec
> Page Faults/sec
>System
> Processor Queue Length
> CPU %
>SQL:
> Cache Hit Ratio
> I/O - Single Page Write/Sec
> I/O - Page Reads/Sec
> I/O - Transactions/Sec
> User Connections
>
>What would you add or remove from this list?
>
>Thanks,
>Bob Castleman
>SuccessWare SoftWare
>
>.
>|||Thanks Steve.
Unfortunately, the info at the link you provided is pretty generic. I guess
I'm looking for specifics based on the experiences of the experts that post
here.
"Steve Hindmarsh" <steve@.nowhere.com> wrote in message
news:019f01c3db96$3e331ba0$a401280a@.phx.gbl...
> Bob,
> I'd take a look at the Microsoft SQL Server 2000
> operations guide - as it has reccommendations for creating
> a performance baseline..
> Take a look at :-
> http://www.microsoft.com/technet/treeview/default.asp?
> url=/technet/prodtechnol/sql/maintain/operate/opsguide/sqlo
> ps5.asp
> and look for 'Creating The Baseline - Suggested Counters'
> Hope this helps.
> >--Original Message--
> >We are in the process of bringing a new SQL Cluster into
> production. One of
> >the things we are insisting on is that we have
> appropriate performance
> >monitoring in place BEFORE our customers start using it.
> >
> >Briefly, the cluster is twin quad xeons, expandable to
> eight processors. 8
> >gig ram expandable to 64. Fiber channel to a drive array,
> log files to a
> >separate array. The cluster is an ASP environment.
> Customers access the
> >database server through a load balanced Citrix farm. The
> expectation is to
> >go from about 100 customers to over 1000 in the next 24
> months, partly
> >through aquiring new customers and partly through moving
> existing customers
> >to the ASP model. Each customer has a separate set of
> databases. The obvious
> >problem is that performance is maintianed as new
> customers are added. The
> >monitoring plan is to take a baseline set of readings
> with no load on the
> >new cluster, then take data at prescribed intervals so
> that we can project
> >utilization and adjust planned upgrades accordingly. All
> standard procedure
> >(or at least it should be, right?).
> >
> >My question is, that if you had the opportunity to set up
> such monitoring on
> >a pristine system, what would you include? We are
> currently going to look at
> >the following:
> >
> >Logical Disk:
> > Avg Queue Length
> > Current Disk Queue
> >Physical Disk
> > Avg Queue Length
> > Current Disk Queue
> >Memory
> > Pages/sec
> > Page Reads/sec
> > Page Faults/sec
> >System
> > Processor Queue Length
> > CPU %
> >SQL:
> > Cache Hit Ratio
> > I/O - Single Page Write/Sec
> > I/O - Page Reads/Sec
> > I/O - Transactions/Sec
> > User Connections
> >
> >
> >
> >What would you add or remove from this list?
> >
> >
> >Thanks,
> >
> >Bob Castleman
> >SuccessWare SoftWare
> >
> >
> >.
> >
Monitoring SqlServer 2000 performance with Express
Hi,
I'm using Visual Web Developer Express and Management Studio Express, and my web site is on a shared web host′, running SqlServer2000. I'm looking for software that enables me to monitor the server, but is it possible? The only apps I've found (and downloaded and installed and unistalled) so far need administrative rights to the server so they won't work on a shared web host.
All help would be welcome!
Thanks in advance,
Pettrer
Goto Administrative Tools...Performance.
Right mouse click on the graph and select "Add Counters..."
Drop down the Performance Objects listbox.
There are many performance counters installed with SQL Server, IIS and .NET.
|||
Hello,
This little app seems quite powerful, but it's only for times when one has physical access and the correct permissions to the db server, isn't it? I can only find SQLServer Express in the list of availabale servers, that is, my own computer's (inactive) web server.
I was thinking more of a third-party purchase, if any of those works in a hosted environment.
Thanks for replying!
Pettrer, Sweden
|||>> but it's only for times when one has physical access and the correct permissions to the db server, isn't it?
A third party tool will also need permissions. In the Add Counters dialog, there's a pull down to select from other computers. However, I admit I've never tried or done this :)
There must be a way to get it to work. From the help:
Choosing the computer to use for monitoring
When monitoring computers remotely, you have some options for how to collect data. For example, you could run performance logging on the administrator's computer, drawing data continuously from each remote computer. In another case, you could have each computer running the service to collect data and, at regular intervals, run a batch program to transfer the data to the administrator's computer for analysis and archiving.
Centralized data collection (that is, collection on a local computer from remote computers that you are monitoring) is simple to implement because only one logging service is running. You can collect data from multiple systems into a single log file. However, it causes additional network traffic and might be restricted by available memory on the administrator's computer. To do centralized data collection, use the Add Counters dialog box to select a remote computer while running System Monitor on your local computer.
Distributed data collection (that is, data collection that occurs on the remote computers you are monitoring) does not incur the memory and network traffic problems of centralized collection. However, it does result in delayed availability of the data, requiring that the collected data be transferred to the administrator's computer for review. To do distributed data collection, use Computer Management on a local computer to select a remote computer on which to collect data.
When monitoring remote computers, note that the remote computer will only allow access to user accounts that have permission to access it. In order to monitor remote systems from your computer, you must start the Performance Logs and Alerts service using an account that has permission to access the remote computers you want to monitor. By default, the service is started under the local computer's "system" account, which generally has permission only to access services and resources on the local computer. To start this under a different account, use the Run As option when configuring data collection, or use Services under Computer Management and update the properties of the Performance Logs and Alerts service.
|||
Hi again,
Thanks for your replies. I found the option you mentioned but didn't see a "remote" option or the like. In any case, I bellieve I wont be able to access the server's system folder anyway. I don't know much about this, but it seems as if the majority of these apps are diving into the system, which may be a prerequisite, at least for diagnosing the internal performance. What I'm looking for is how well the server performs, how long before it jams and so on, and it might not be possible to it this way.
Pettrer
Monday, March 19, 2012
Monitoring SQL Server
I believe we are reaching some limitations with SQL Server and I have been monitoring certain items in the Performance Monitor such as: pages/sec; Bytes received/sec; Bytes sent/sec; % disk read time; % disk write time; % processor time; Log growths; percent log used; and transactions/sec. I notice quite a few spikes in Bytes sent/sec and when the % disk read time spikes for more than a few seconds, users notice a delay.
My thoughts are that 1: We need more memory on our SQL Server box (we currently have 768 meg, need a faster SQL Server box and need to distribute the load of some databases to another SQL Server; and 2: We also have a bottleneck when users are connecting via Citrix to SQL Server via our Terminal2 server (which has been tracked down to simply a slow Terminal2 box with a slow nic card - This has been confirmed that our Terminal2 is definately taking a toll and will time-out when large queries are executed.)
We also have been monitoring each of the server boxes. Are there any other recommendations for SQL Server Performance monitor that anyone could see which would be good to monitor (there are several things which can be selected to monitor?)
We've also noticed that bound MSAccess forms seem to play a significant role in the long spikes for Bytes sent/sec. I'm assuming this might be normal for bound forms and the slow SQL Server box with limited memory. Unbound MSAccess forms do not seem to present any problem and show as quick spikes for the Performance monitor.
Another problem is that I also can't seem to tie back the Performance monitor spikes with specific transactions in the SQL Profiler. Is there any way to pinpoint a spike in the Performance monitor with a specific transaction other than trying to catch the spike and quickly switching to SQL Profiler?
We are planning on upgrading our SQL Server box and also adding in another SQL Server box to help distribute the load with certain databases. We are also getting a faster box for our Terminal2 (citrix) server as these slow-downs/time-outs do not happen internally or when we use Remote Desktop Connection to connect externally (only when we connect externally via Terminal2).
Any help would be greatly appreciated! Thank you in advance.Have you done a sql trace?
1/2 GB of memory?
That's kinda low...
MS Access and SQL Server...hmmmm|||I'm sorry, I meant running a SQL Trace and the Performance Monitor. Is there a way to tie these 2 together to find spikes in Performance Monitor related to a specific transaction in the SQL Trace?
My thoughts are that 1: We need more memory on our SQL Server box (we currently have 768 meg, need a faster SQL Server box and need to distribute the load of some databases to another SQL Server; and 2: We also have a bottleneck when users are connecting via Citrix to SQL Server via our Terminal2 server (which has been tracked down to simply a slow Terminal2 box with a slow nic card - This has been confirmed that our Terminal2 is definately taking a toll and will time-out when large queries are executed.)
We also have been monitoring each of the server boxes. Are there any other recommendations for SQL Server Performance monitor that anyone could see which would be good to monitor (there are several things which can be selected to monitor?)
We've also noticed that bound MSAccess forms seem to play a significant role in the long spikes for Bytes sent/sec. I'm assuming this might be normal for bound forms and the slow SQL Server box with limited memory. Unbound MSAccess forms do not seem to present any problem and show as quick spikes for the Performance monitor.
Another problem is that I also can't seem to tie back the Performance monitor spikes with specific transactions in the SQL Profiler. Is there any way to pinpoint a spike in the Performance monitor with a specific transaction other than trying to catch the spike and quickly switching to SQL Profiler?
We are planning on upgrading our SQL Server box and also adding in another SQL Server box to help distribute the load with certain databases. We are also getting a faster box for our Terminal2 (citrix) server as these slow-downs/time-outs do not happen internally or when we use Remote Desktop Connection to connect externally (only when we connect externally via Terminal2).
Any help would be greatly appreciated! Thank you in advance.Have you done a sql trace?
1/2 GB of memory?
That's kinda low...
MS Access and SQL Server...hmmmm|||I'm sorry, I meant running a SQL Trace and the Performance Monitor. Is there a way to tie these 2 together to find spikes in Performance Monitor related to a specific transaction in the SQL Trace?
Monitoring SQL Compact Edition Performance
Does SQL Compact Edition expose performance counters to tools like Perfmon as SQL Server does? For instance, can you view lock wait times, cache hit ratio, etc.?
Currently, SQL Compact does not expose any performance counters. It is a in-process database engine, not a service/server process like SQL Server. You can analyze query execution with the query analyzer in SQL Server Management Studio (Express SP2).Monitoring server performance during nightly Analysis Services processing
Hello all,
We would like to get figures about the server performance when processing Analysis Services database. The processing takes place nightly. The prosessing is automatic and takes place outside of office hours. So there's nobody in the office who could monitor the server performance.
We'd like to know for example the following issues.
- memory usage in the server
- page file usage (reads / writes)
- CPU load (we have 4 processors)
- suitable data available of Analysis Server (don't know if there's any)
I know these are very high level requirements... What I have in mind is that we would have something similar to "CPU usage history" and "Page File Usage History" in Windows Task manager, but for a longer time period. I've tried to find a suitable tool for this, but I've had no luck and have found nothing. I know that many others must have had similar problems.
So, how to get the figures? Any suggestions would be highly respected.
r,
JM
One easy way to capture all this is to schedule performance logging on your server. All the counters you mention above are available as well as many that are specific to SSAS.
To set up a log, go to Computer Management and expand Performance Logs and Alerts. Right-click Counter Logs and select New Log Settings. Give the log a meaningful name, e.g. "SSAS Nightly Logging". Add the counters of interest, set the sampling rate and a file location/format, and schedule the logging for the period of interest.
Setting the sampling rate appropriately can be tricky. Try to push it to as high a duration as you can stand because there is overhead with each sample. Also, consider how you want to consume the output. In the past, I've dumped the data to flat file and set up a little data mart and ETL layer to present the data in a simple star-schema for slicing and dicing.
Bryan
|||Hello,
Thanks fot the advice, the idea really works.
We use now Performance Monitor to log the selected counter values to a file and later we use Performance Monitor to analyze the files. This approach is enough for our purposes now.
Seems that it's not possible to collect the performance data to a binary format log file and at the same time open the file for analysis in Performance Monitor. We have to stop the data collection first. I'm not totally sure about this. Logging and reading the log file simultaneously would be needed to make real time monitoring possible so that also history data would be available.
r,
J
|||There are a few ways you can address this. The simplest is to cap the size of the performance log files and have the logging start a new log once the max size is reached. You can then open and consume the old files closer real-time. However, it's still not real time.
Another way is to interface with WMI. This is a little tricky and will require some custom dev work. I believe this is how Microsoft Operations Management (MOM) works (which is another option for you).
Yet another good option is to open the perfmon utility, add the counters, and monitor these in real-time through there.
Hope that helps,
Bryan
Monitoring server performance during nightly Analysis Services processing
Hello all,
We would like to get figures about the server performance when processing Analysis Services database. The processing takes place nightly. The prosessing is automatic and takes place outside of office hours. So there's nobody in the office who could monitor the server performance.
We'd like to know for example the following issues.
- memory usage in the server
- page file usage (reads / writes)
- CPU load (we have 4 processors)
- suitable data available of Analysis Server (don't know if there's any)
I know these are very high level requirements... What I have in mind is that we would have something similar to "CPU usage history" and "Page File Usage History" in Windows Task manager, but for a longer time period. I've tried to find a suitable tool for this, but I've had no luck and have found nothing. I know that many others must have had similar problems.
So, how to get the figures? Any suggestions would be highly respected.
r,
JM
One easy way to capture all this is to schedule performance logging on your server. All the counters you mention above are available as well as many that are specific to SSAS.
To set up a log, go to Computer Management and expand Performance Logs and Alerts. Right-click Counter Logs and select New Log Settings. Give the log a meaningful name, e.g. "SSAS Nightly Logging". Add the counters of interest, set the sampling rate and a file location/format, and schedule the logging for the period of interest.
Setting the sampling rate appropriately can be tricky. Try to push it to as high a duration as you can stand because there is overhead with each sample. Also, consider how you want to consume the output. In the past, I've dumped the data to flat file and set up a little data mart and ETL layer to present the data in a simple star-schema for slicing and dicing.
Bryan
|||Hello,
Thanks fot the advice, the idea really works.
We use now Performance Monitor to log the selected counter values to a file and later we use Performance Monitor to analyze the files. This approach is enough for our purposes now.
Seems that it's not possible to collect the performance data to a binary format log file and at the same time open the file for analysis in Performance Monitor. We have to stop the data collection first. I'm not totally sure about this. Logging and reading the log file simultaneously would be needed to make real time monitoring possible so that also history data would be available.
r,
J
|||There are a few ways you can address this. The simplest is to cap the size of the performance log files and have the logging start a new log once the max size is reached. You can then open and consume the old files closer real-time. However, it's still not real time.
Another way is to interface with WMI. This is a little tricky and will require some custom dev work. I believe this is how Microsoft Operations Management (MOM) works (which is another option for you).
Yet another good option is to open the perfmon utility, add the counters, and monitor these in real-time through there.
Hope that helps,
Bryan
Monitoring Query Performance
All,
I have a question regarding query performance. I have a SQL 2000 server that
is acting as a backend for a business reporting application (IB WebFocus).
Most of the time, the system runs along at a fairly descent clip, but there
are times when our users (who might be new to WebFocus) submit queries that
hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
usage, and I end up hving to kill some sessions (in the reporting app) before
CPU usage is back under control.
I would like to try and be more proactive than that... what are some ways I
can identify the queries that are running when this happens? Is setting up a
trace file my best bet, or...?
Thanks in advance...
David
monitor the sysprocess system table. You can also use sp_who or sp_who2 to
see the contents of this table.
look for processes that have gone awry...or are blocking other users. Use
net send to notify the user...then Use kill <spid> to kick them.
"David Levine" <DavidLevine@.discussions.microsoft.com> wrote in message
news:953DFEF8-18E6-4BA1-AD4B-436310DE5E09@.microsoft.com...
> All,
> I have a question regarding query performance. I have a SQL 2000 server
> that
> is acting as a backend for a business reporting application (IB WebFocus).
> Most of the time, the system runs along at a fairly descent clip, but
> there
> are times when our users (who might be new to WebFocus) submit queries
> that
> hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
> usage, and I end up hving to kill some sessions (in the reporting app)
> before
> CPU usage is back under control.
> I would like to try and be more proactive than that... what are some ways
> I
> can identify the queries that are running when this happens? Is setting up
> a
> trace file my best bet, or...?
> Thanks in advance...
> David
|||Yes...pretty much. I had the same issue a couple of years
ago at a client site using WebFocus. It always turned out to
be the ad hoc queries that the users ran through the tool -
and some of the ugly ways the query was built by the tool
before sending it to SQL. You'll need to trace to find the
issues.
-Sue
On Tue, 24 Jan 2006 15:26:13 -0800, David Levine
<DavidLevine@.discussions.microsoft.com> wrote:
>All,
>I have a question regarding query performance. I have a SQL 2000 server that
>is acting as a backend for a business reporting application (IB WebFocus).
>Most of the time, the system runs along at a fairly descent clip, but there
>are times when our users (who might be new to WebFocus) submit queries that
>hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
>usage, and I end up hving to kill some sessions (in the reporting app) before
>CPU usage is back under control.
>I would like to try and be more proactive than that... what are some ways I
>can identify the queries that are running when this happens? Is setting up a
>trace file my best bet, or...?
>Thanks in advance...
>David
I have a question regarding query performance. I have a SQL 2000 server that
is acting as a backend for a business reporting application (IB WebFocus).
Most of the time, the system runs along at a fairly descent clip, but there
are times when our users (who might be new to WebFocus) submit queries that
hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
usage, and I end up hving to kill some sessions (in the reporting app) before
CPU usage is back under control.
I would like to try and be more proactive than that... what are some ways I
can identify the queries that are running when this happens? Is setting up a
trace file my best bet, or...?
Thanks in advance...
David
monitor the sysprocess system table. You can also use sp_who or sp_who2 to
see the contents of this table.
look for processes that have gone awry...or are blocking other users. Use
net send to notify the user...then Use kill <spid> to kick them.
"David Levine" <DavidLevine@.discussions.microsoft.com> wrote in message
news:953DFEF8-18E6-4BA1-AD4B-436310DE5E09@.microsoft.com...
> All,
> I have a question regarding query performance. I have a SQL 2000 server
> that
> is acting as a backend for a business reporting application (IB WebFocus).
> Most of the time, the system runs along at a fairly descent clip, but
> there
> are times when our users (who might be new to WebFocus) submit queries
> that
> hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
> usage, and I end up hving to kill some sessions (in the reporting app)
> before
> CPU usage is back under control.
> I would like to try and be more proactive than that... what are some ways
> I
> can identify the queries that are running when this happens? Is setting up
> a
> trace file my best bet, or...?
> Thanks in advance...
> David
|||Yes...pretty much. I had the same issue a couple of years
ago at a client site using WebFocus. It always turned out to
be the ad hoc queries that the users ran through the tool -
and some of the ugly ways the query was built by the tool
before sending it to SQL. You'll need to trace to find the
issues.
-Sue
On Tue, 24 Jan 2006 15:26:13 -0800, David Levine
<DavidLevine@.discussions.microsoft.com> wrote:
>All,
>I have a question regarding query performance. I have a SQL 2000 server that
>is acting as a backend for a business reporting application (IB WebFocus).
>Most of the time, the system runs along at a fairly descent clip, but there
>are times when our users (who might be new to WebFocus) submit queries that
>hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
>usage, and I end up hving to kill some sessions (in the reporting app) before
>CPU usage is back under control.
>I would like to try and be more proactive than that... what are some ways I
>can identify the queries that are running when this happens? Is setting up a
>trace file my best bet, or...?
>Thanks in advance...
>David
Monitoring Query Performance
Is there any table in SQL Server 2000 which lists the queries currently
cached in memory and statistics on those queries e.g. execution time, number
of executions cost, etc. I would like to be able to determine the queries
that have the highest cost to target for performance tuning first.
Not really. You have some information in master..syscacheobjects, but that are only the cached plans
(those that are cached in the first place, of course). You can have a profiler trace running to pick
up the measures you are interested in and to your tuning based on that profiler trace.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:C5531A9C-7344-4734-B041-7A83FA31F5F8@.microsoft.com...
> Is there any table in SQL Server 2000 which lists the queries currently
> cached in memory and statistics on those queries e.g. execution time, number
> of executions cost, etc. I would like to be able to determine the queries
> that have the highest cost to target for performance tuning first.
cached in memory and statistics on those queries e.g. execution time, number
of executions cost, etc. I would like to be able to determine the queries
that have the highest cost to target for performance tuning first.
Not really. You have some information in master..syscacheobjects, but that are only the cached plans
(those that are cached in the first place, of course). You can have a profiler trace running to pick
up the measures you are interested in and to your tuning based on that profiler trace.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:C5531A9C-7344-4734-B041-7A83FA31F5F8@.microsoft.com...
> Is there any table in SQL Server 2000 which lists the queries currently
> cached in memory and statistics on those queries e.g. execution time, number
> of executions cost, etc. I would like to be able to determine the queries
> that have the highest cost to target for performance tuning first.
Monday, March 12, 2012
Monitoring Query Performance
All,
I have a question regarding query performance. I have a SQL 2000 server that
is acting as a backend for a business reporting application (IB WebFocus).
Most of the time, the system runs along at a fairly descent clip, but there
are times when our users (who might be new to WebFocus) submit queries that
hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
usage, and I end up hving to kill some sessions (in the reporting app) before
CPU usage is back under control.
I would like to try and be more proactive than that... what are some ways I
can identify the queries that are running when this happens? Is setting up a
trace file my best bet, or...?
Thanks in advance...
Davidmonitor the sysprocess system table. You can also use sp_who or sp_who2 to
see the contents of this table.
look for processes that have gone awry...or are blocking other users. Use
net send to notify the user...then Use kill <spid> to kick them.
"David Levine" <DavidLevine@.discussions.microsoft.com> wrote in message
news:953DFEF8-18E6-4BA1-AD4B-436310DE5E09@.microsoft.com...
> All,
> I have a question regarding query performance. I have a SQL 2000 server
> that
> is acting as a backend for a business reporting application (IB WebFocus).
> Most of the time, the system runs along at a fairly descent clip, but
> there
> are times when our users (who might be new to WebFocus) submit queries
> that
> hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
> usage, and I end up hving to kill some sessions (in the reporting app)
> before
> CPU usage is back under control.
> I would like to try and be more proactive than that... what are some ways
> I
> can identify the queries that are running when this happens? Is setting up
> a
> trace file my best bet, or...?
> Thanks in advance...
> David|||Yes...pretty much. I had the same issue a couple of years
ago at a client site using WebFocus. It always turned out to
be the ad hoc queries that the users ran through the tool -
and some of the ugly ways the query was built by the tool
before sending it to SQL. You'll need to trace to find the
issues.
-Sue
On Tue, 24 Jan 2006 15:26:13 -0800, David Levine
<DavidLevine@.discussions.microsoft.com> wrote:
>All,
>I have a question regarding query performance. I have a SQL 2000 server that
>is acting as a backend for a business reporting application (IB WebFocus).
>Most of the time, the system runs along at a fairly descent clip, but there
>are times when our users (who might be new to WebFocus) submit queries that
>hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
>usage, and I end up hving to kill some sessions (in the reporting app) before
>CPU usage is back under control.
>I would like to try and be more proactive than that... what are some ways I
>can identify the queries that are running when this happens? Is setting up a
>trace file my best bet, or...?
>Thanks in advance...
>David
I have a question regarding query performance. I have a SQL 2000 server that
is acting as a backend for a business reporting application (IB WebFocus).
Most of the time, the system runs along at a fairly descent clip, but there
are times when our users (who might be new to WebFocus) submit queries that
hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
usage, and I end up hving to kill some sessions (in the reporting app) before
CPU usage is back under control.
I would like to try and be more proactive than that... what are some ways I
can identify the queries that are running when this happens? Is setting up a
trace file my best bet, or...?
Thanks in advance...
Davidmonitor the sysprocess system table. You can also use sp_who or sp_who2 to
see the contents of this table.
look for processes that have gone awry...or are blocking other users. Use
net send to notify the user...then Use kill <spid> to kick them.
"David Levine" <DavidLevine@.discussions.microsoft.com> wrote in message
news:953DFEF8-18E6-4BA1-AD4B-436310DE5E09@.microsoft.com...
> All,
> I have a question regarding query performance. I have a SQL 2000 server
> that
> is acting as a backend for a business reporting application (IB WebFocus).
> Most of the time, the system runs along at a fairly descent clip, but
> there
> are times when our users (who might be new to WebFocus) submit queries
> that
> hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
> usage, and I end up hving to kill some sessions (in the reporting app)
> before
> CPU usage is back under control.
> I would like to try and be more proactive than that... what are some ways
> I
> can identify the queries that are running when this happens? Is setting up
> a
> trace file my best bet, or...?
> Thanks in advance...
> David|||Yes...pretty much. I had the same issue a couple of years
ago at a client site using WebFocus. It always turned out to
be the ad hoc queries that the users ran through the tool -
and some of the ugly ways the query was built by the tool
before sending it to SQL. You'll need to trace to find the
issues.
-Sue
On Tue, 24 Jan 2006 15:26:13 -0800, David Levine
<DavidLevine@.discussions.microsoft.com> wrote:
>All,
>I have a question regarding query performance. I have a SQL 2000 server that
>is acting as a backend for a business reporting application (IB WebFocus).
>Most of the time, the system runs along at a fairly descent clip, but there
>are times when our users (who might be new to WebFocus) submit queries that
>hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
>usage, and I end up hving to kill some sessions (in the reporting app) before
>CPU usage is back under control.
>I would like to try and be more proactive than that... what are some ways I
>can identify the queries that are running when this happens? Is setting up a
>trace file my best bet, or...?
>Thanks in advance...
>David
Monitoring Query Performance
Is there any table in SQL Server 2000 which lists the queries currently
cached in memory and statistics on those queries e.g. execution time, number
of executions cost, etc. I would like to be able to determine the queries
that have the highest cost to target for performance tuning first.Not really. You have some information in master..syscacheobjects, but that are only the cached plans
(those that are cached in the first place, of course). You can have a profiler trace running to pick
up the measures you are interested in and to your tuning based on that profiler trace.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:C5531A9C-7344-4734-B041-7A83FA31F5F8@.microsoft.com...
> Is there any table in SQL Server 2000 which lists the queries currently
> cached in memory and statistics on those queries e.g. execution time, number
> of executions cost, etc. I would like to be able to determine the queries
> that have the highest cost to target for performance tuning first.
cached in memory and statistics on those queries e.g. execution time, number
of executions cost, etc. I would like to be able to determine the queries
that have the highest cost to target for performance tuning first.Not really. You have some information in master..syscacheobjects, but that are only the cached plans
(those that are cached in the first place, of course). You can have a profiler trace running to pick
up the measures you are interested in and to your tuning based on that profiler trace.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:C5531A9C-7344-4734-B041-7A83FA31F5F8@.microsoft.com...
> Is there any table in SQL Server 2000 which lists the queries currently
> cached in memory and statistics on those queries e.g. execution time, number
> of executions cost, etc. I would like to be able to determine the queries
> that have the highest cost to target for performance tuning first.
Monitoring Query Performance
All,
I have a question regarding query performance. I have a SQL 2000 server that
is acting as a backend for a business reporting application (IB WebFocus).
Most of the time, the system runs along at a fairly descent clip, but there
are times when our users (who might be new to WebFocus) submit queries that
hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
usage, and I end up hving to kill some sessions (in the reporting app) befor
e
CPU usage is back under control.
I would like to try and be more proactive than that... what are some ways I
can identify the queries that are running when this happens? Is setting up a
trace file my best bet, or...?
Thanks in advance...
Davidmonitor the sysprocess system table. You can also use sp_who or sp_who2 to
see the contents of this table.
look for processes that have gone awry...or are blocking other users. Use
net send to notify the user...then Use kill <spid> to kick them.
"David Levine" <DavidLevine@.discussions.microsoft.com> wrote in message
news:953DFEF8-18E6-4BA1-AD4B-436310DE5E09@.microsoft.com...
> All,
> I have a question regarding query performance. I have a SQL 2000 server
> that
> is acting as a backend for a business reporting application (IB WebFocus).
> Most of the time, the system runs along at a fairly descent clip, but
> there
> are times when our users (who might be new to WebFocus) submit queries
> that
> hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
> usage, and I end up hving to kill some sessions (in the reporting app)
> before
> CPU usage is back under control.
> I would like to try and be more proactive than that... what are some ways
> I
> can identify the queries that are running when this happens? Is setting up
> a
> trace file my best bet, or...?
> Thanks in advance...
> David|||Yes...pretty much. I had the same issue a couple of years
ago at a client site using WebFocus. It always turned out to
be the ad hoc queries that the users ran through the tool -
and some of the ugly ways the query was built by the tool
before sending it to SQL. You'll need to trace to find the
issues.
-Sue
On Tue, 24 Jan 2006 15:26:13 -0800, David Levine
<DavidLevine@.discussions.microsoft.com> wrote:
>All,
>I have a question regarding query performance. I have a SQL 2000 server tha
t
>is acting as a backend for a business reporting application (IB WebFocus).
>Most of the time, the system runs along at a fairly descent clip, but there
>are times when our users (who might be new to WebFocus) submit queries that
>hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
>usage, and I end up hving to kill some sessions (in the reporting app) befo
re
>CPU usage is back under control.
>I would like to try and be more proactive than that... what are some ways
I
>can identify the queries that are running when this happens? Is setting up
a
>trace file my best bet, or...?
>Thanks in advance...
>David
I have a question regarding query performance. I have a SQL 2000 server that
is acting as a backend for a business reporting application (IB WebFocus).
Most of the time, the system runs along at a fairly descent clip, but there
are times when our users (who might be new to WebFocus) submit queries that
hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
usage, and I end up hving to kill some sessions (in the reporting app) befor
e
CPU usage is back under control.
I would like to try and be more proactive than that... what are some ways I
can identify the queries that are running when this happens? Is setting up a
trace file my best bet, or...?
Thanks in advance...
Davidmonitor the sysprocess system table. You can also use sp_who or sp_who2 to
see the contents of this table.
look for processes that have gone awry...or are blocking other users. Use
net send to notify the user...then Use kill <spid> to kick them.
"David Levine" <DavidLevine@.discussions.microsoft.com> wrote in message
news:953DFEF8-18E6-4BA1-AD4B-436310DE5E09@.microsoft.com...
> All,
> I have a question regarding query performance. I have a SQL 2000 server
> that
> is acting as a backend for a business reporting application (IB WebFocus).
> Most of the time, the system runs along at a fairly descent clip, but
> there
> are times when our users (who might be new to WebFocus) submit queries
> that
> hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
> usage, and I end up hving to kill some sessions (in the reporting app)
> before
> CPU usage is back under control.
> I would like to try and be more proactive than that... what are some ways
> I
> can identify the queries that are running when this happens? Is setting up
> a
> trace file my best bet, or...?
> Thanks in advance...
> David|||Yes...pretty much. I had the same issue a couple of years
ago at a client site using WebFocus. It always turned out to
be the ad hoc queries that the users ran through the tool -
and some of the ugly ways the query was built by the tool
before sending it to SQL. You'll need to trace to find the
issues.
-Sue
On Tue, 24 Jan 2006 15:26:13 -0800, David Levine
<DavidLevine@.discussions.microsoft.com> wrote:
>All,
>I have a question regarding query performance. I have a SQL 2000 server tha
t
>is acting as a backend for a business reporting application (IB WebFocus).
>Most of the time, the system runs along at a fairly descent clip, but there
>are times when our users (who might be new to WebFocus) submit queries that
>hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
>usage, and I end up hving to kill some sessions (in the reporting app) befo
re
>CPU usage is back under control.
>I would like to try and be more proactive than that... what are some ways
I
>can identify the queries that are running when this happens? Is setting up
a
>trace file my best bet, or...?
>Thanks in advance...
>David
Monitoring Query Performance
Is there any table in SQL Server 2000 which lists the queries currently
cached in memory and statistics on those queries e.g. execution time, number
of executions cost, etc. I would like to be able to determine the queries
that have the highest cost to target for performance tuning first.Not really. You have some information in master..syscacheobjects, but that a
re only the cached plans
(those that are cached in the first place, of course). You can have a profil
er trace running to pick
up the measures you are interested in and to your tuning based on that profi
ler trace.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:C5531A9C-7344-4734-B041-7A83FA31F5F8@.microsoft.com...
> Is there any table in SQL Server 2000 which lists the queries currently
> cached in memory and statistics on those queries e.g. execution time, numb
er
> of executions cost, etc. I would like to be able to determine the queries
> that have the highest cost to target for performance tuning first.
cached in memory and statistics on those queries e.g. execution time, number
of executions cost, etc. I would like to be able to determine the queries
that have the highest cost to target for performance tuning first.Not really. You have some information in master..syscacheobjects, but that a
re only the cached plans
(those that are cached in the first place, of course). You can have a profil
er trace running to pick
up the measures you are interested in and to your tuning based on that profi
ler trace.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:C5531A9C-7344-4734-B041-7A83FA31F5F8@.microsoft.com...
> Is there any table in SQL Server 2000 which lists the queries currently
> cached in memory and statistics on those queries e.g. execution time, numb
er
> of executions cost, etc. I would like to be able to determine the queries
> that have the highest cost to target for performance tuning first.
Friday, March 9, 2012
Monitoring Cache
In most of the projects/companies these days, Disk space is not an issue, but
many a times its the cache that hampers lot of performance. Even though
people think they have enough disk space, they should not expect any
performance issue, which is not true.
How do I ensure that I use the cache properly; what tools (counters in those
tools) can I use to ensure the cache is used well. Also, is there anyway I
can check these data on a database which is backed-up and provided to me for
offline analysis.
Any Help please
TIA
PP
You can monitor memory usage in general with DBCC MEMORYSTATUS. Search
Google for documentation on what the output means.
Andrew J. Kelly SQL MVP
"PP" <PP@.discussions.microsoft.com> wrote in message
news:C820A2E4-98DA-4F65-AE67-0D83E6900830@.microsoft.com...
> In most of the projects/companies these days, Disk space is not an issue,
> but
> many a times its the cache that hampers lot of performance. Even though
> people think they have enough disk space, they should not expect any
> performance issue, which is not true.
> How do I ensure that I use the cache properly; what tools (counters in
> those
> tools) can I use to ensure the cache is used well. Also, is there anyway
> I
> can check these data on a database which is backed-up and provided to me
> for
> offline analysis.
> Any Help please
> TIA
> PP
>
many a times its the cache that hampers lot of performance. Even though
people think they have enough disk space, they should not expect any
performance issue, which is not true.
How do I ensure that I use the cache properly; what tools (counters in those
tools) can I use to ensure the cache is used well. Also, is there anyway I
can check these data on a database which is backed-up and provided to me for
offline analysis.
Any Help please
TIA
PP
You can monitor memory usage in general with DBCC MEMORYSTATUS. Search
Google for documentation on what the output means.
Andrew J. Kelly SQL MVP
"PP" <PP@.discussions.microsoft.com> wrote in message
news:C820A2E4-98DA-4F65-AE67-0D83E6900830@.microsoft.com...
> In most of the projects/companies these days, Disk space is not an issue,
> but
> many a times its the cache that hampers lot of performance. Even though
> people think they have enough disk space, they should not expect any
> performance issue, which is not true.
> How do I ensure that I use the cache properly; what tools (counters in
> those
> tools) can I use to ensure the cache is used well. Also, is there anyway
> I
> can check these data on a database which is backed-up and provided to me
> for
> offline analysis.
> Any Help please
> TIA
> PP
>
Monitoring Cache
In most of the projects/companies these days, Disk space is not an issue, bu
t
many a times its the cache that hampers lot of performance. Even though
people think they have enough disk space, they should not expect any
performance issue, which is not true.
How do I ensure that I use the cache properly; what tools (counters in those
tools) can I use to ensure the cache is used well. Also, is there anyway I
can check these data on a database which is backed-up and provided to me for
offline analysis.
Any Help please
TIA
PPYou can monitor memory usage in general with DBCC MEMORYSTATUS. Search
Google for documentation on what the output means.
Andrew J. Kelly SQL MVP
"PP" <PP@.discussions.microsoft.com> wrote in message
news:C820A2E4-98DA-4F65-AE67-0D83E6900830@.microsoft.com...
> In most of the projects/companies these days, Disk space is not an issue,
> but
> many a times its the cache that hampers lot of performance. Even though
> people think they have enough disk space, they should not expect any
> performance issue, which is not true.
> How do I ensure that I use the cache properly; what tools (counters in
> those
> tools) can I use to ensure the cache is used well. Also, is there anyway
> I
> can check these data on a database which is backed-up and provided to me
> for
> offline analysis.
> Any Help please
> TIA
> PP
>
t
many a times its the cache that hampers lot of performance. Even though
people think they have enough disk space, they should not expect any
performance issue, which is not true.
How do I ensure that I use the cache properly; what tools (counters in those
tools) can I use to ensure the cache is used well. Also, is there anyway I
can check these data on a database which is backed-up and provided to me for
offline analysis.
Any Help please
TIA
PPYou can monitor memory usage in general with DBCC MEMORYSTATUS. Search
Google for documentation on what the output means.
Andrew J. Kelly SQL MVP
"PP" <PP@.discussions.microsoft.com> wrote in message
news:C820A2E4-98DA-4F65-AE67-0D83E6900830@.microsoft.com...
> In most of the projects/companies these days, Disk space is not an issue,
> but
> many a times its the cache that hampers lot of performance. Even though
> people think they have enough disk space, they should not expect any
> performance issue, which is not true.
> How do I ensure that I use the cache properly; what tools (counters in
> those
> tools) can I use to ensure the cache is used well. Also, is there anyway
> I
> can check these data on a database which is backed-up and provided to me
> for
> offline analysis.
> Any Help please
> TIA
> PP
>
Monitoring Cache
In most of the projects/companies these days, Disk space is not an issue, but
many a times its the cache that hampers lot of performance. Even though
people think they have enough disk space, they should not expect any
performance issue, which is not true.
How do I ensure that I use the cache properly; what tools (counters in those
tools) can I use to ensure the cache is used well. Also, is there anyway I
can check these data on a database which is backed-up and provided to me for
offline analysis.
Any Help please
TIA
PPYou can monitor memory usage in general with DBCC MEMORYSTATUS. Search
Google for documentation on what the output means.
--
Andrew J. Kelly SQL MVP
"PP" <PP@.discussions.microsoft.com> wrote in message
news:C820A2E4-98DA-4F65-AE67-0D83E6900830@.microsoft.com...
> In most of the projects/companies these days, Disk space is not an issue,
> but
> many a times its the cache that hampers lot of performance. Even though
> people think they have enough disk space, they should not expect any
> performance issue, which is not true.
> How do I ensure that I use the cache properly; what tools (counters in
> those
> tools) can I use to ensure the cache is used well. Also, is there anyway
> I
> can check these data on a database which is backed-up and provided to me
> for
> offline analysis.
> Any Help please
> TIA
> PP
>
many a times its the cache that hampers lot of performance. Even though
people think they have enough disk space, they should not expect any
performance issue, which is not true.
How do I ensure that I use the cache properly; what tools (counters in those
tools) can I use to ensure the cache is used well. Also, is there anyway I
can check these data on a database which is backed-up and provided to me for
offline analysis.
Any Help please
TIA
PPYou can monitor memory usage in general with DBCC MEMORYSTATUS. Search
Google for documentation on what the output means.
--
Andrew J. Kelly SQL MVP
"PP" <PP@.discussions.microsoft.com> wrote in message
news:C820A2E4-98DA-4F65-AE67-0D83E6900830@.microsoft.com...
> In most of the projects/companies these days, Disk space is not an issue,
> but
> many a times its the cache that hampers lot of performance. Even though
> people think they have enough disk space, they should not expect any
> performance issue, which is not true.
> How do I ensure that I use the cache properly; what tools (counters in
> those
> tools) can I use to ensure the cache is used well. Also, is there anyway
> I
> can check these data on a database which is backed-up and provided to me
> for
> offline analysis.
> Any Help please
> TIA
> PP
>
Subscribe to:
Posts (Atom)