How can I setup Profiler to track/watch the execution of the lines in a
stored procedure?
It looks like I just see exec sp_DoWork, as TextData.
I want to see the sql statements in the SP and how long they each take.
Thanks,
ThomasLL
Hi,
I am not sure whether this is possible...
But as soon as you have identified the SP thats taking time, why dont
you check the execution plan of that SP instead?
Thomas.LeBlanc@.NoSpam.Com wrote:
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL
|||Hi Thomas
You can trace SP:StmtStarting and SP:StmtCompleted
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL
sql
Showing posts with label setup. Show all posts
Showing posts with label setup. Show all posts
Friday, March 30, 2012
Profiler - details of SP
How can I setup Profiler to track/watch the execution of the lines in a
stored procedure?
It looks like I just see exec sp_DoWork, as TextData.
I want to see the sql statements in the SP and how long they each take.
--
Thanks,
ThomasLLHi,
I am not sure whether this is possible...
But as soon as you have identified the SP thats taking time, why dont
you check the execution plan of that SP instead?
Thomas.LeBlanc@.NoSpam.Com wrote:
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||Hi Thomas
You can trace SP:StmtStarting and SP:StmtCompleted
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL
stored procedure?
It looks like I just see exec sp_DoWork, as TextData.
I want to see the sql statements in the SP and how long they each take.
--
Thanks,
ThomasLLHi,
I am not sure whether this is possible...
But as soon as you have identified the SP thats taking time, why dont
you check the execution plan of that SP instead?
Thomas.LeBlanc@.NoSpam.Com wrote:
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||Hi Thomas
You can trace SP:StmtStarting and SP:StmtCompleted
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL
Profiler - details of SP
How can I setup Profiler to track/watch the execution of the lines in a
stored procedure?
It looks like I just see exec sp_DoWork, as TextData.
I want to see the sql statements in the SP and how long they each take.
--
Thanks,
ThomasLLHi,
I am not sure whether this is possible...
But as soon as you have identified the SP thats taking time, why dont
you check the execution plan of that SP instead?
Thomas.LeBlanc@.NoSpam.Com wrote:
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||Hi Thomas
You can trace SP:StmtStarting and SP:StmtCompleted
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||That gives me the duration of each Insert, Updaet and Select in the SP?
Thanks Karen, you are a blessing to the SQL Server community
--
Thanks,
Thomas
"Kalen Delaney" wrote:
> Hi Thomas
> You can trace SP:StmtStarting and SP:StmtCompleted
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Thomas.LeBlanc@.NoSpam.Com"
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
> news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> > How can I setup Profiler to track/watch the execution of the lines in a
> > stored procedure?
> >
> > It looks like I just see exec sp_DoWork, as TextData.
> >
> > I want to see the sql statements in the SP and how long they each take.
> > --
> > Thanks,
> > ThomasLL
>
>|||SP:StmtCompleted can give you the duration of each statement in the SP.
Thanks for your kind words. Karen thanks you too. ;-)
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:CC7D6F54-4DE7-4DE8-B7CC-7904572FDF90@.microsoft.com...
> That gives me the duration of each Insert, Updaet and Select in the SP?
>
> Thanks Karen, you are a blessing to the SQL Server community
> --
> Thanks,
> Thomas
>
> "Kalen Delaney" wrote:
>> Hi Thomas
>> You can trace SP:StmtStarting and SP:StmtCompleted
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "Thomas.LeBlanc@.NoSpam.Com"
>> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
>> news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
>> > How can I setup Profiler to track/watch the execution of the lines in a
>> > stored procedure?
>> >
>> > It looks like I just see exec sp_DoWork, as TextData.
>> >
>> > I want to see the sql statements in the SP and how long they each take.
>> > --
>> > Thanks,
>> > ThomasLL
>>
stored procedure?
It looks like I just see exec sp_DoWork, as TextData.
I want to see the sql statements in the SP and how long they each take.
--
Thanks,
ThomasLLHi,
I am not sure whether this is possible...
But as soon as you have identified the SP thats taking time, why dont
you check the execution plan of that SP instead?
Thomas.LeBlanc@.NoSpam.Com wrote:
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||Hi Thomas
You can trace SP:StmtStarting and SP:StmtCompleted
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||That gives me the duration of each Insert, Updaet and Select in the SP?
Thanks Karen, you are a blessing to the SQL Server community
--
Thanks,
Thomas
"Kalen Delaney" wrote:
> Hi Thomas
> You can trace SP:StmtStarting and SP:StmtCompleted
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Thomas.LeBlanc@.NoSpam.Com"
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
> news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> > How can I setup Profiler to track/watch the execution of the lines in a
> > stored procedure?
> >
> > It looks like I just see exec sp_DoWork, as TextData.
> >
> > I want to see the sql statements in the SP and how long they each take.
> > --
> > Thanks,
> > ThomasLL
>
>|||SP:StmtCompleted can give you the duration of each statement in the SP.
Thanks for your kind words. Karen thanks you too. ;-)
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:CC7D6F54-4DE7-4DE8-B7CC-7904572FDF90@.microsoft.com...
> That gives me the duration of each Insert, Updaet and Select in the SP?
>
> Thanks Karen, you are a blessing to the SQL Server community
> --
> Thanks,
> Thomas
>
> "Kalen Delaney" wrote:
>> Hi Thomas
>> You can trace SP:StmtStarting and SP:StmtCompleted
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "Thomas.LeBlanc@.NoSpam.Com"
>> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
>> news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
>> > How can I setup Profiler to track/watch the execution of the lines in a
>> > stored procedure?
>> >
>> > It looks like I just see exec sp_DoWork, as TextData.
>> >
>> > I want to see the sql statements in the SP and how long they each take.
>> > --
>> > Thanks,
>> > ThomasLL
>>
Monday, March 12, 2012
Processing cube from SSAS - OLE DB error
I have made a new dimension and setup the reference in dimension usage. Every time I process the cube i got the following message
Error 1 OLE DB error: OLE DB or ODBC error: Invalid column name 'PReference'.; 42S22. 0 0
The value in this column is 0,1 or 2. I have set the value in all rows to 1 and still get the same error.
I have tried to create a new column - updated value to 1 and refreshed the datasource and changed the dimension and dimension usage and I still got the same error processing the cube.
I have tried to use another integer field in dimension usage and this works fine.
Maybe it is something on the table - i have tried almost all but I can't solve this problem.
Leo Pedersen
The error message "Invalid column name 'PReference'" is saying that an invalid column name was specified in the SQL query that AS generated. Run Profiler on the AS server and capture the SQL query. It should point you to the problem.Monday, February 20, 2012
Procedure taking longer due to Service Packs applied to SQL Server 7
I have a job setup to run a procedure in a database. The user account
sa is the owner. The actual database resides on an NT Server 4.0
Terminal Services PC using SQL Server 7.
The first time I scheduled it the job took approx 6 minutes to
complete and allowing it to continue averaged at approx 6 minutes.
I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
with no Service Packs applied. So I applied the Service Packs to the
environment and then restored the database I was using.
Running the same job again which executes the procedure took approx 20
minutes and allowing it to continue averaged at approx 20 minutes.
I need to find out what difference the Service Packs make to the SQL
environment as this is the only thing that has changed. I'm not saying
this is a SQL issue as it may be that the procedure needs amending in
some way to make it run more efficiently.
Any feedback or advice would be grateful.
Try updating statistics on your tables, and see if that makes a difference.
Just a stab in the dark.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rzirpolo" <robert.zirpolo@.phoenixdb.co.uk> wrote in message
news:5320d330.0408050041.5c815b24@.posting.google.c om...
> I have a job setup to run a procedure in a database. The user account
> sa is the owner. The actual database resides on an NT Server 4.0
> Terminal Services PC using SQL Server 7.
> The first time I scheduled it the job took approx 6 minutes to
> complete and allowing it to continue averaged at approx 6 minutes.
> I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
> with no Service Packs applied. So I applied the Service Packs to the
> environment and then restored the database I was using.
> Running the same job again which executes the procedure took approx 20
> minutes and allowing it to continue averaged at approx 20 minutes.
> I need to find out what difference the Service Packs make to the SQL
> environment as this is the only thing that has changed. I'm not saying
> this is a SQL issue as it may be that the procedure needs amending in
> some way to make it run more efficiently.
> Any feedback or advice would be grateful.
|||Looks a very good stab in the dark, thank you.
Upon enabling the job again the job has now reduced back down to 6
minutes.
I'm not to clued up on the SQL Server admin side and am wondering what
exactly are these statistics and what did this procedure do i.e. what
does updating them do ?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||From SQL 200 BOL:
"The distribution statistics are used by Microsoft SQL ServerT to optimize
navigation through tables during the processing of Transact-SQL statements.
To build the distribution statistics automatically, SQL Server periodically
samples a percentage of the data in the corresponding table for each index."
From "Distribution Statistics" in SQL 2000 BOL:
"All indexes have distribution statistics that describe the selectivity and
distribution of the key values in the index. Selectivity is a property that
relates to how many rows are typically identified by a key value. A unique
key has high selectivity; a key value found in 1,000 rows has poor
selectivity. The selectivity and distribution statistics are used by
Microsoft SQL ServerT 2000 to optimize its navigation through tables and
indexed views when processing Transact-SQL statements. The distribution
statistics are used to estimate how efficient an index would be in
retrieving data associated with a key value or range specified in the query.
The statistics for each index are not limited to a single page but are
stored as a long string of bits across multiple pages in the same way image
data is stored. The column sysindexes.statblob points to this distribution
data. You can use the DBCC SHOW_STATISTICS statement to get a report on the
distribution statistics for an index.
Distribution statistics may also be maintained for unindexed columns. These
can be defined manually using the CREATE STATISTICS statement or created
automatically by the query optimizer. Statistics on unindexed columns count
against the limit of 249 nonclustered indexes allowed on a table.
To be useful to query optimizer, distribution statistics must be kept
reasonably current. The distribution statistics should be refreshed anytime
significant numbers of changes to keys occur in the index. Distribution
statistics can be updated manually using the UPDATE STATISTICS statement.
SQL Server 2000 can also detect when distribution statistics are out of date
and update the statistics automatically. This update is performed by the
task that detected that the statistics needed to be updated. The update is
performed using a complex sampling method that minimizes the effect of the
update on transaction throughput."
Appiles to SQL 7 and 2000 alike.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"microsoft.public.sqlserver.server" <robert.zirpolo@.phoenixdb.co.uk> wrote
in message news:OHmihz7eEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Looks a very good stab in the dark, thank you.
> Upon enabling the job again the job has now reduced back down to 6
> minutes.
> I'm not to clued up on the SQL Server admin side and am wondering what
> exactly are these statistics and what did this procedure do i.e. what
> does updating them do ?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sa is the owner. The actual database resides on an NT Server 4.0
Terminal Services PC using SQL Server 7.
The first time I scheduled it the job took approx 6 minutes to
complete and allowing it to continue averaged at approx 6 minutes.
I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
with no Service Packs applied. So I applied the Service Packs to the
environment and then restored the database I was using.
Running the same job again which executes the procedure took approx 20
minutes and allowing it to continue averaged at approx 20 minutes.
I need to find out what difference the Service Packs make to the SQL
environment as this is the only thing that has changed. I'm not saying
this is a SQL issue as it may be that the procedure needs amending in
some way to make it run more efficiently.
Any feedback or advice would be grateful.
Try updating statistics on your tables, and see if that makes a difference.
Just a stab in the dark.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rzirpolo" <robert.zirpolo@.phoenixdb.co.uk> wrote in message
news:5320d330.0408050041.5c815b24@.posting.google.c om...
> I have a job setup to run a procedure in a database. The user account
> sa is the owner. The actual database resides on an NT Server 4.0
> Terminal Services PC using SQL Server 7.
> The first time I scheduled it the job took approx 6 minutes to
> complete and allowing it to continue averaged at approx 6 minutes.
> I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
> with no Service Packs applied. So I applied the Service Packs to the
> environment and then restored the database I was using.
> Running the same job again which executes the procedure took approx 20
> minutes and allowing it to continue averaged at approx 20 minutes.
> I need to find out what difference the Service Packs make to the SQL
> environment as this is the only thing that has changed. I'm not saying
> this is a SQL issue as it may be that the procedure needs amending in
> some way to make it run more efficiently.
> Any feedback or advice would be grateful.
|||Looks a very good stab in the dark, thank you.
Upon enabling the job again the job has now reduced back down to 6
minutes.
I'm not to clued up on the SQL Server admin side and am wondering what
exactly are these statistics and what did this procedure do i.e. what
does updating them do ?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||From SQL 200 BOL:
"The distribution statistics are used by Microsoft SQL ServerT to optimize
navigation through tables during the processing of Transact-SQL statements.
To build the distribution statistics automatically, SQL Server periodically
samples a percentage of the data in the corresponding table for each index."
From "Distribution Statistics" in SQL 2000 BOL:
"All indexes have distribution statistics that describe the selectivity and
distribution of the key values in the index. Selectivity is a property that
relates to how many rows are typically identified by a key value. A unique
key has high selectivity; a key value found in 1,000 rows has poor
selectivity. The selectivity and distribution statistics are used by
Microsoft SQL ServerT 2000 to optimize its navigation through tables and
indexed views when processing Transact-SQL statements. The distribution
statistics are used to estimate how efficient an index would be in
retrieving data associated with a key value or range specified in the query.
The statistics for each index are not limited to a single page but are
stored as a long string of bits across multiple pages in the same way image
data is stored. The column sysindexes.statblob points to this distribution
data. You can use the DBCC SHOW_STATISTICS statement to get a report on the
distribution statistics for an index.
Distribution statistics may also be maintained for unindexed columns. These
can be defined manually using the CREATE STATISTICS statement or created
automatically by the query optimizer. Statistics on unindexed columns count
against the limit of 249 nonclustered indexes allowed on a table.
To be useful to query optimizer, distribution statistics must be kept
reasonably current. The distribution statistics should be refreshed anytime
significant numbers of changes to keys occur in the index. Distribution
statistics can be updated manually using the UPDATE STATISTICS statement.
SQL Server 2000 can also detect when distribution statistics are out of date
and update the statistics automatically. This update is performed by the
task that detected that the statistics needed to be updated. The update is
performed using a complex sampling method that minimizes the effect of the
update on transaction throughput."
Appiles to SQL 7 and 2000 alike.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"microsoft.public.sqlserver.server" <robert.zirpolo@.phoenixdb.co.uk> wrote
in message news:OHmihz7eEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Looks a very good stab in the dark, thank you.
> Upon enabling the job again the job has now reduced back down to 6
> minutes.
> I'm not to clued up on the SQL Server admin side and am wondering what
> exactly are these statistics and what did this procedure do i.e. what
> does updating them do ?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Procedure taking longer due to Service Packs applied to SQL Server 7
I have a job setup to run a procedure in a database. The user account
sa is the owner. The actual database resides on an NT Server 4.0
Terminal Services PC using SQL Server 7.
The first time I scheduled it the job took approx 6 minutes to
complete and allowing it to continue averaged at approx 6 minutes.
I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
with no Service Packs applied. So I applied the Service Packs to the
environment and then restored the database I was using.
Running the same job again which executes the procedure took approx 20
minutes and allowing it to continue averaged at approx 20 minutes.
I need to find out what difference the Service Packs make to the SQL
environment as this is the only thing that has changed. I'm not saying
this is a SQL issue as it may be that the procedure needs amending in
some way to make it run more efficiently.
Any feedback or advice would be grateful.Try updating statistics on your tables, and see if that makes a difference.
Just a stab in the dark.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rzirpolo" <robert.zirpolo@.phoenixdb.co.uk> wrote in message
news:5320d330.0408050041.5c815b24@.posting.google.com...
> I have a job setup to run a procedure in a database. The user account
> sa is the owner. The actual database resides on an NT Server 4.0
> Terminal Services PC using SQL Server 7.
> The first time I scheduled it the job took approx 6 minutes to
> complete and allowing it to continue averaged at approx 6 minutes.
> I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
> with no Service Packs applied. So I applied the Service Packs to the
> environment and then restored the database I was using.
> Running the same job again which executes the procedure took approx 20
> minutes and allowing it to continue averaged at approx 20 minutes.
> I need to find out what difference the Service Packs make to the SQL
> environment as this is the only thing that has changed. I'm not saying
> this is a SQL issue as it may be that the procedure needs amending in
> some way to make it run more efficiently.
> Any feedback or advice would be grateful.|||Looks a very good stab in the dark, thank you.
Upon enabling the job again the job has now reduced back down to 6
minutes.
I'm not to clued up on the SQL Server admin side and am wondering what
exactly are these statistics and what did this procedure do i.e. what
does updating them do ?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||From SQL 200 BOL:
"The distribution statistics are used by Microsoft SQL ServerT to optimize
navigation through tables during the processing of Transact-SQL statements.
To build the distribution statistics automatically, SQL Server periodically
samples a percentage of the data in the corresponding table for each index."
From "Distribution Statistics" in SQL 2000 BOL:
"All indexes have distribution statistics that describe the selectivity and
distribution of the key values in the index. Selectivity is a property that
relates to how many rows are typically identified by a key value. A unique
key has high selectivity; a key value found in 1,000 rows has poor
selectivity. The selectivity and distribution statistics are used by
Microsoft SQL ServerT 2000 to optimize its navigation through tables and
indexed views when processing Transact-SQL statements. The distribution
statistics are used to estimate how efficient an index would be in
retrieving data associated with a key value or range specified in the query.
The statistics for each index are not limited to a single page but are
stored as a long string of bits across multiple pages in the same way image
data is stored. The column sysindexes.statblob points to this distribution
data. You can use the DBCC SHOW_STATISTICS statement to get a report on the
distribution statistics for an index.
Distribution statistics may also be maintained for unindexed columns. These
can be defined manually using the CREATE STATISTICS statement or created
automatically by the query optimizer. Statistics on unindexed columns count
against the limit of 249 nonclustered indexes allowed on a table.
To be useful to query optimizer, distribution statistics must be kept
reasonably current. The distribution statistics should be refreshed anytime
significant numbers of changes to keys occur in the index. Distribution
statistics can be updated manually using the UPDATE STATISTICS statement.
SQL Server 2000 can also detect when distribution statistics are out of date
and update the statistics automatically. This update is performed by the
task that detected that the statistics needed to be updated. The update is
performed using a complex sampling method that minimizes the effect of the
update on transaction throughput."
Appiles to SQL 7 and 2000 alike.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"microsoft.public.sqlserver.server" <robert.zirpolo@.phoenixdb.co.uk> wrote
in message news:OHmihz7eEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Looks a very good stab in the dark, thank you.
> Upon enabling the job again the job has now reduced back down to 6
> minutes.
> I'm not to clued up on the SQL Server admin side and am wondering what
> exactly are these statistics and what did this procedure do i.e. what
> does updating them do ?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sa is the owner. The actual database resides on an NT Server 4.0
Terminal Services PC using SQL Server 7.
The first time I scheduled it the job took approx 6 minutes to
complete and allowing it to continue averaged at approx 6 minutes.
I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
with no Service Packs applied. So I applied the Service Packs to the
environment and then restored the database I was using.
Running the same job again which executes the procedure took approx 20
minutes and allowing it to continue averaged at approx 20 minutes.
I need to find out what difference the Service Packs make to the SQL
environment as this is the only thing that has changed. I'm not saying
this is a SQL issue as it may be that the procedure needs amending in
some way to make it run more efficiently.
Any feedback or advice would be grateful.Try updating statistics on your tables, and see if that makes a difference.
Just a stab in the dark.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rzirpolo" <robert.zirpolo@.phoenixdb.co.uk> wrote in message
news:5320d330.0408050041.5c815b24@.posting.google.com...
> I have a job setup to run a procedure in a database. The user account
> sa is the owner. The actual database resides on an NT Server 4.0
> Terminal Services PC using SQL Server 7.
> The first time I scheduled it the job took approx 6 minutes to
> complete and allowing it to continue averaged at approx 6 minutes.
> I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
> with no Service Packs applied. So I applied the Service Packs to the
> environment and then restored the database I was using.
> Running the same job again which executes the procedure took approx 20
> minutes and allowing it to continue averaged at approx 20 minutes.
> I need to find out what difference the Service Packs make to the SQL
> environment as this is the only thing that has changed. I'm not saying
> this is a SQL issue as it may be that the procedure needs amending in
> some way to make it run more efficiently.
> Any feedback or advice would be grateful.|||Looks a very good stab in the dark, thank you.
Upon enabling the job again the job has now reduced back down to 6
minutes.
I'm not to clued up on the SQL Server admin side and am wondering what
exactly are these statistics and what did this procedure do i.e. what
does updating them do ?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||From SQL 200 BOL:
"The distribution statistics are used by Microsoft SQL ServerT to optimize
navigation through tables during the processing of Transact-SQL statements.
To build the distribution statistics automatically, SQL Server periodically
samples a percentage of the data in the corresponding table for each index."
From "Distribution Statistics" in SQL 2000 BOL:
"All indexes have distribution statistics that describe the selectivity and
distribution of the key values in the index. Selectivity is a property that
relates to how many rows are typically identified by a key value. A unique
key has high selectivity; a key value found in 1,000 rows has poor
selectivity. The selectivity and distribution statistics are used by
Microsoft SQL ServerT 2000 to optimize its navigation through tables and
indexed views when processing Transact-SQL statements. The distribution
statistics are used to estimate how efficient an index would be in
retrieving data associated with a key value or range specified in the query.
The statistics for each index are not limited to a single page but are
stored as a long string of bits across multiple pages in the same way image
data is stored. The column sysindexes.statblob points to this distribution
data. You can use the DBCC SHOW_STATISTICS statement to get a report on the
distribution statistics for an index.
Distribution statistics may also be maintained for unindexed columns. These
can be defined manually using the CREATE STATISTICS statement or created
automatically by the query optimizer. Statistics on unindexed columns count
against the limit of 249 nonclustered indexes allowed on a table.
To be useful to query optimizer, distribution statistics must be kept
reasonably current. The distribution statistics should be refreshed anytime
significant numbers of changes to keys occur in the index. Distribution
statistics can be updated manually using the UPDATE STATISTICS statement.
SQL Server 2000 can also detect when distribution statistics are out of date
and update the statistics automatically. This update is performed by the
task that detected that the statistics needed to be updated. The update is
performed using a complex sampling method that minimizes the effect of the
update on transaction throughput."
Appiles to SQL 7 and 2000 alike.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"microsoft.public.sqlserver.server" <robert.zirpolo@.phoenixdb.co.uk> wrote
in message news:OHmihz7eEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Looks a very good stab in the dark, thank you.
> Upon enabling the job again the job has now reduced back down to 6
> minutes.
> I'm not to clued up on the SQL Server admin side and am wondering what
> exactly are these statistics and what did this procedure do i.e. what
> does updating them do ?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Procedure taking longer due to Service Packs applied to SQL Server 7
I have a job setup to run a procedure in a database. The user account
sa is the owner. The actual database resides on an NT Server 4.0
Terminal Services PC using SQL Server 7.
The first time I scheduled it the job took approx 6 minutes to
complete and allowing it to continue averaged at approx 6 minutes.
I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
with no Service Packs applied. So I applied the Service Packs to the
environment and then restored the database I was using.
Running the same job again which executes the procedure took approx 20
minutes and allowing it to continue averaged at approx 20 minutes.
I need to find out what difference the Service Packs make to the SQL
environment as this is the only thing that has changed. I'm not saying
this is a SQL issue as it may be that the procedure needs amending in
some way to make it run more efficiently.
Any feedback or advice would be grateful.Try updating statistics on your tables, and see if that makes a difference.
Just a stab in the dark.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rzirpolo" <robert.zirpolo@.phoenixdb.co.uk> wrote in message
news:5320d330.0408050041.5c815b24@.posting.google.com...
> I have a job setup to run a procedure in a database. The user account
> sa is the owner. The actual database resides on an NT Server 4.0
> Terminal Services PC using SQL Server 7.
> The first time I scheduled it the job took approx 6 minutes to
> complete and allowing it to continue averaged at approx 6 minutes.
> I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
> with no Service Packs applied. So I applied the Service Packs to the
> environment and then restored the database I was using.
> Running the same job again which executes the procedure took approx 20
> minutes and allowing it to continue averaged at approx 20 minutes.
> I need to find out what difference the Service Packs make to the SQL
> environment as this is the only thing that has changed. I'm not saying
> this is a SQL issue as it may be that the procedure needs amending in
> some way to make it run more efficiently.
> Any feedback or advice would be grateful.
sa is the owner. The actual database resides on an NT Server 4.0
Terminal Services PC using SQL Server 7.
The first time I scheduled it the job took approx 6 minutes to
complete and allowing it to continue averaged at approx 6 minutes.
I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
with no Service Packs applied. So I applied the Service Packs to the
environment and then restored the database I was using.
Running the same job again which executes the procedure took approx 20
minutes and allowing it to continue averaged at approx 20 minutes.
I need to find out what difference the Service Packs make to the SQL
environment as this is the only thing that has changed. I'm not saying
this is a SQL issue as it may be that the procedure needs amending in
some way to make it run more efficiently.
Any feedback or advice would be grateful.Try updating statistics on your tables, and see if that makes a difference.
Just a stab in the dark.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rzirpolo" <robert.zirpolo@.phoenixdb.co.uk> wrote in message
news:5320d330.0408050041.5c815b24@.posting.google.com...
> I have a job setup to run a procedure in a database. The user account
> sa is the owner. The actual database resides on an NT Server 4.0
> Terminal Services PC using SQL Server 7.
> The first time I scheduled it the job took approx 6 minutes to
> complete and allowing it to continue averaged at approx 6 minutes.
> I realised that I was running SQL version 7.00.623 i.e. SQL Server 7
> with no Service Packs applied. So I applied the Service Packs to the
> environment and then restored the database I was using.
> Running the same job again which executes the procedure took approx 20
> minutes and allowing it to continue averaged at approx 20 minutes.
> I need to find out what difference the Service Packs make to the SQL
> environment as this is the only thing that has changed. I'm not saying
> this is a SQL issue as it may be that the procedure needs amending in
> some way to make it run more efficiently.
> Any feedback or advice would be grateful.
Subscribe to:
Posts (Atom)