Question Update statistics

Hi,
1. What is the main difference between update statistics and create
statistics?
2. If I use "update statistics" on a table that does not have any statistics
created, will it create statistics? If yes, what is the difference between
update statistics and create statistics?
3. I take it that if I use update statistics it will only update statistics
that are already exiting.
4. Does update statistics, sp_update stats, sp_createstats and create
statistics create/update statistics on indexes as well?
Thanks,
Jay
"Jay S." <JayS@.discussions.microsoft.com> wrote in message
news:2ECC6B03-31E6-48AF-A5E4-6338179193B0@.microsoft.com...
> Hi,
> 1. What is the main difference between update statistics and create
> statistics?
Update refreshes existing stats. Create makes new ones.

> 2. If I use "update statistics" on a table that does not have any
> statistics
> created, will it create statistics? If yes, what is the difference between
> update statistics and create statistics?
No.

> 3. I take it that if I use update statistics it will only update
> statistics
> that are already exiting.
>
Yes.

> 4. Does update statistics, sp_update stats, sp_createstats and create
> statistics create/update statistics on indexes as well?
sp_updatestats updates stats on an index(only if they are "outdated" in
2005)
> Thanks,
> Jay
Check out this for more info:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio

Question Update statistics

Hi,
1. What is the main difference between update statistics and create
statistics?
2. If I use "update statistics" on a table that does not have any statistics
created, will it create statistics? If yes, what is the difference between
update statistics and create statistics?
3. I take it that if I use update statistics it will only update statistics
that are already exiting.
4. Does update statistics, sp_update stats, sp_createstats and create
statistics create/update statistics on indexes as well?
Thanks,
Jay"Jay S." <JayS@.discussions.microsoft.com> wrote in message
news:2ECC6B03-31E6-48AF-A5E4-6338179193B0@.microsoft.com...
> Hi,
> 1. What is the main difference between update statistics and create
> statistics?
Update refreshes existing stats. Create makes new ones.

> 2. If I use "update statistics" on a table that does not have any
> statistics
> created, will it create statistics? If yes, what is the difference between
> update statistics and create statistics?
No.

> 3. I take it that if I use update statistics it will only update
> statistics
> that are already exiting.
>
Yes.

> 4. Does update statistics, sp_update stats, sp_createstats and create
> statistics create/update statistics on indexes as well?
sp_updatestats updates stats on an index(only if they are "outdated" in
2005)
> Thanks,
> Jay
Check out this for more info:
http://www.microsoft.com/technet/pr...5/qrystats.mspx
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio

Question Update statistics

Hi,
1. What is the main difference between update statistics and create
statistics?
2. If I use "update statistics" on a table that does not have any statistics
created, will it create statistics? If yes, what is the difference between
update statistics and create statistics?
3. I take it that if I use update statistics it will only update statistics
that are already exiting.
4. Does update statistics, sp_update stats, sp_createstats and create
statistics create/update statistics on indexes as well?
Thanks,
Jay"Jay S." <JayS@.discussions.microsoft.com> wrote in message
news:2ECC6B03-31E6-48AF-A5E4-6338179193B0@.microsoft.com...
> Hi,
> 1. What is the main difference between update statistics and create
> statistics?
Update refreshes existing stats. Create makes new ones.
> 2. If I use "update statistics" on a table that does not have any
> statistics
> created, will it create statistics? If yes, what is the difference between
> update statistics and create statistics?
No.
> 3. I take it that if I use update statistics it will only update
> statistics
> that are already exiting.
>
Yes.
> 4. Does update statistics, sp_update stats, sp_createstats and create
> statistics create/update statistics on indexes as well?
sp_updatestats updates stats on an index(only if they are "outdated" in
2005)
> Thanks,
> Jay
Check out this for more info:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio

Question regarding Sql Server memory and cursors

Hello,

We make use of ADO (not ADO.NET) and server-side cursors in one of our products and when viewing Performance Statistics for Sql Server 2005 using Windows Performance Monitor we have noticed that the "Number of active cursor plans" never decreases. In our queries we maintain several open, server-side cursors concurrently. We use .NET/C# and the standard ADO import library. We are closing our recordsets.

The number of active plans jumps by 40 or 50 for each of our queries and as far as we can tell, never decreases (the number of Active Cursors, however, does decrease back to zero.) MSDN/Documentation was somewhat cryptic on what an Active Cursor Plan actually is and Google didn't have any useful information, either.

So, the question is, does this matter? What is an Active Cursor Plan? Is there something other than the recordset and connection that needs to be closed?

Any information or a reference to any useful documentation would be greatly appreciated.

Thanks a lot,

George Kondiles

See this http://blogs.msdn.com/sqlprogrammability/archive/2006/04/27/585170.aspx that says everything.

Question on SET STATISTICS IO ON

Howdy all. I was wondering about the output I'm getting from using this
command both before and after adding an index to speed up a sproc. Here is
the output (for the table in question) before an index is added:
Table 'HomeStudyResponses'. Scan count 70341, logical reads 238646, physical
reads 0, read-ahead reads 0.
and here is the output after after the index is added:
Table 'HomeStudyResponses'. Scan count 2, logical reads 352493, physical
reads 0, read-ahead reads 0.
It also now has a "worktable" result, which did not exist before the index
was added:
Table 'Worktable'. Scan count 1, logical reads 4, physical reads 0,
read-ahead reads 0.
As you see, the Scans after the index was added decreased significantly by
adding the index, but it also increased the Logical Reads. I've been looking
at this article (
http://www.sql-server-performance.com/articles/per/statistics_io_time_p3.aspx
) and it says that what matters most is the Logical Reads. It goes so far as
to say that an increase in Logical Reads wil actually hurt performance.
However, it doesnt say anything about Scans being lowered this much but
Logical Reads going up at the same time.
So my questions (finally):
1. Why would Scans be affected by adding an index? After all, a Scan refers
to the number of times that tables referenced in the query have been accessed.
2. As a general rule, are more Logical Reads OK if the number of Scans drops
tremendously?
TIA, ChrisR> 1. Why would Scans be affected by adding an index? After all, a Scan
> refers
> to the number of times that tables referenced in the query have been
> accessed.
It appears that before you added the index it was doing a nested loop join
or an index seek for each matching row. After it appears to scan the entire
table or index twice. It's hard to say without seeing the query plan.
> 2. As a general rule, are more Logical Reads OK if the number of Scans
> drops tremendously?
That typically means you are scanning and that may be OK or it may not. The
goal is really to reduce the number of reads as much as possible.
Can you post the DDL for the table & Indexes along with the query you are
using?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BAB3E39F-DB82-4375-AAE4-9428D550002A@.microsoft.com...
> Howdy all. I was wondering about the output I'm getting from using this
> command both before and after adding an index to speed up a sproc. Here is
> the output (for the table in question) before an index is added:
> Table 'HomeStudyResponses'. Scan count 70341, logical reads 238646,
> physical
> reads 0, read-ahead reads 0.
>
> and here is the output after after the index is added:
> Table 'HomeStudyResponses'. Scan count 2, logical reads 352493, physical
> reads 0, read-ahead reads 0.
> It also now has a "worktable" result, which did not exist before the index
> was added:
> Table 'Worktable'. Scan count 1, logical reads 4, physical reads 0,
> read-ahead reads 0.
> As you see, the Scans after the index was added decreased significantly by
> adding the index, but it also increased the Logical Reads. I've been
> looking
> at this article (
> http://www.sql-server-performance.com/articles/per/statistics_io_time_p3.aspx
> ) and it says that what matters most is the Logical Reads. It goes so far
> as
> to say that an increase in Logical Reads wil actually hurt performance.
> However, it doesnt say anything about Scans being lowered this much but
> Logical Reads going up at the same time.
> So my questions (finally):
> 1. Why would Scans be affected by adding an index? After all, a Scan
> refers
> to the number of times that tables referenced in the query have been
> accessed.
> 2. As a general rule, are more Logical Reads OK if the number of Scans
> drops
> tremendously?
> TIA, ChrisR
>
>|||ChrisR,
In general, a lower logical read count is better, because potentially
each logical read is a physical read.
But that theoretical model for the worst case situation almost never
applies in reality. You will have a buffer cache that is very likely to
cache at least some of the pages of the table you are querying. If there
is a clustered index scan or table scan, then each logical read
represent reading a page. For a large table, the buffer cache hit ratio
might be low. If there are many index seeks for a loop join, then the
logical reads of the index seek refers to finding a row on a page, and
these logical reads are likely to have a much higher buffer cache hit
ratio, especially for the index root and branches. Also, processing a
cached logical "page" read will require more CPU then processing a
cached "row" read.
Unfortunately, SQL Server will only report the total number of logical
reads, and will not tell if they are "page" logical reads and/or "row"
logical reads.
> Table 'HomeStudyResponses'. Scan count 70341, logical reads 238646, physical
> reads 0, read-ahead reads 0.
The scan count suggests that there were 70341 index seeks, requiring
(almost) 238646 logical row reads.
> Table 'HomeStudyResponses'. Scan count 2, logical reads 352493, physical
> reads 0, read-ahead reads 0.
If the query has only one reference to the HomeStudyResponses table,
then the scan count suggest that a parallel query plan was executed and
that 2 processors scanned (or partially scanned) the table/index,
requiring (almost) 352493 logical page reads.
Now for your questions:
> 1. Why would Scans be affected by adding an index? After all, a Scan refers
> to the number of times that tables referenced in the query have been accessed.
Correct. But if you add an index, it gives the optimizer more options
how to execute the query. In your case, it looks like the optimizer
switched from using a Loop Join to a Merge Join or Hash Join. It looks
like a bad switch, because the logical read count has gone up, and now
their are probably "page" reads instead of earlier "row" reads.
Of course, the real test is to measure the performance. You should test
this. In the end it is only (average) execution duration that matters.
> 2. As a general rule, are more Logical Reads OK if the number of Scans drops
> tremendously?
No, definitely not. If all you have is logical reads, then lower is
better. For the same number of logical reads, logical "row" reads are
better than logical "page" reads (because of the higher likelyhood of a
cache hit, and the lower CPU overhead). The number of scans doesn't
directly affect performance, at least not significantly.
--
Gert-Jan|||On Aug 29, 1:00 pm, ChrisR <Chr...@.discussions.microsoft.com> wrote:
> Howdy all. I was wondering about the output I'm getting from using this
> command both before and after adding an index to speed up a sproc. Here is
> the output (for the table in question) before an index is added:
> Table 'HomeStudyResponses'. Scan count 70341, logical reads 238646, physical
> reads 0, read-ahead reads 0.
> and here is the output after after the index is added:
> Table 'HomeStudyResponses'. Scan count 2, logical reads 352493, physical
> reads 0, read-ahead reads 0.
> It also now has a "worktable" result, which did not exist before the index
> was added:
> Table 'Worktable'. Scan count 1, logical reads 4, physical reads 0,
> read-ahead reads 0.
> As you see, the Scans after the index was added decreased significantly by
> adding the index, but it also increased the Logical Reads. I've been looking
> at this article (http://www.sql-server-performance.com/articles/per/statistics_io_time...
> ) and it says that what matters most is the Logical Reads. It goes so far as
> to say that an increase in Logical Reads wil actually hurt performance.
> However, it doesnt say anything about Scans being lowered this much but
> Logical Reads going up at the same time.
> So my questions (finally):
> 1. Why would Scans be affected by adding an index? After all, a Scan refers
> to the number of times that tables referenced in the query have been accessed.
> 2. As a general rule, are more Logical Reads OK if the number of Scans drops
> tremendously?
> TIA, ChrisR
Statistics IO is just part of the story. I always use it together with
STATISTICS TIME on. CPU is a precious resource and saving CPU cycles
is very important, in some cases more important than saving logical
reads. Sequential reads can be very fast, and more reads may be not
too bad. Also index pages may be more likely to be found in the cache
- that is something the optimizer can never know. i would solve
problems like this on case-by-case basis without making general
statements if possible.
Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/|||Thanks to all!!!
"ChrisR" wrote:
> Howdy all. I was wondering about the output I'm getting from using this
> command both before and after adding an index to speed up a sproc. Here is
> the output (for the table in question) before an index is added:
> Table 'HomeStudyResponses'. Scan count 70341, logical reads 238646, physical
> reads 0, read-ahead reads 0.
>
> and here is the output after after the index is added:
> Table 'HomeStudyResponses'. Scan count 2, logical reads 352493, physical
> reads 0, read-ahead reads 0.
> It also now has a "worktable" result, which did not exist before the index
> was added:
> Table 'Worktable'. Scan count 1, logical reads 4, physical reads 0,
> read-ahead reads 0.
> As you see, the Scans after the index was added decreased significantly by
> adding the index, but it also increased the Logical Reads. I've been looking
> at this article (
> http://www.sql-server-performance.com/articles/per/statistics_io_time_p3.aspx
> ) and it says that what matters most is the Logical Reads. It goes so far as
> to say that an increase in Logical Reads wil actually hurt performance.
> However, it doesnt say anything about Scans being lowered this much but
> Logical Reads going up at the same time.
> So my questions (finally):
> 1. Why would Scans be affected by adding an index? After all, a Scan refers
> to the number of times that tables referenced in the query have been accessed.
> 2. As a general rule, are more Logical Reads OK if the number of Scans drops
> tremendously?
> TIA, ChrisR
>
>

Question on data optimizations

The database maintenance plan wizards present the ability to do either
data/index page reorgs or update statistics.
Is there any reason you can't do both?
Is it because an update of the statistics is automatically done on a
reorg?
Thanks in advance.
Gerry
Hi

> Is it because an update of the statistics is automatically done on a
> reorg?
When SQL Server rebuild indexes ,statistics ares automatically to be
updated
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegr oups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
|||DataPro wrote:
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
Reindexing will update the statistics...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hello,
You are correct; Statistics will be updated as part of reindex.
Thanks
Hari
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegr oups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
|||Thanks much to all.

Question on data optimizations

The database maintenance plan wizards present the ability to do either
data/index page reorgs or update statistics.
Is there any reason you can't do both?
Is it because an update of the statistics is automatically done on a
reorg?
Thanks in advance.
GerryHi

> Is it because an update of the statistics is automatically done on a
> reorg?
When SQL Server rebuild indexes ,statistics ares automatically to be
updated
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||DataPro wrote:
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
Reindexing will update the statistics...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hello,
You are correct; Statistics will be updated as part of reindex.
Thanks
Hari
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||Thanks much to all.

Question on data optimizations

The database maintenance plan wizards present the ability to do either
data/index page reorgs or update statistics.
Is there any reason you can't do both?
Is it because an update of the statistics is automatically done on a
reorg?
Thanks in advance.
GerryHi
> Is it because an update of the statistics is automatically done on a
> reorg?
When SQL Server rebuild indexes ,statistics ares automatically to be
updated
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||DataPro wrote:
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
Reindexing will update the statistics...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hello,
You are correct; Statistics will be updated as part of reindex.
Thanks
Hari
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||Thanks much to all.