Hi,
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.
Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>
|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>
|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
Showing posts with label available. Show all posts
Showing posts with label available. Show all posts
Monday, March 26, 2012
Performance question: Indexes on separate file group or dimension table on separate fi
Hi,
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--sql
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--sql
Tuesday, March 20, 2012
Performance problem but memory available...
Hi,
I've some performance problems on a server.
I monitor these statistics:
Pages/sec
page faults/sec
available MBytes
the result is strange during small activities on the server: (min, avg, max)
Pages/sec = 0, 30, 217
page faults/sec = 18, 350, 4000
available MBytes = 290Mb, 300,Mb 308Mb
My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
1280Mb installed.
Opening the property page of SQL server to manage the memory could take 2 to
3 minutes before I can see anything!!!
I've the same problem with some client applications, a simple query can take
a long time, and another time (5minutes) the same query takes only 5 seconds
after a reboot.
If I restart my server, then all works fine, but when my SQL Server instance
start to consume more memory, then the performance slow down. I don't
understand why, because I've some memory available.
What can I monitor to identify the problem?
Thanks for your guides.
Jrme.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09What is the processor utilization?... Sounds like you've got something
eating it all up!
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:utqpL3V9DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've some performance problems on a server.
> I monitor these statistics:
> Pages/sec
> page faults/sec
> available MBytes
> the result is strange during small activities on the server: (min, avg,
max)
> Pages/sec = 0, 30, 217
> page faults/sec = 18, 350, 4000
> available MBytes = 290Mb, 300,Mb 308Mb
> My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
> 1280Mb installed.
> Opening the property page of SQL server to manage the memory could take 2
to
> 3 minutes before I can see anything!!!
> I've the same problem with some client applications, a simple query can
take
> a long time, and another time (5minutes) the same query takes only 5
seconds
> after a reboot.
> If I restart my server, then all works fine, but when my SQL Server
instance
> start to consume more memory, then the performance slow down. I don't
> understand why, because I've some memory available.
> What can I monitor to identify the problem?
> Thanks for your guides.
> Jrme.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09
>
I've some performance problems on a server.
I monitor these statistics:
Pages/sec
page faults/sec
available MBytes
the result is strange during small activities on the server: (min, avg, max)
Pages/sec = 0, 30, 217
page faults/sec = 18, 350, 4000
available MBytes = 290Mb, 300,Mb 308Mb
My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
1280Mb installed.
Opening the property page of SQL server to manage the memory could take 2 to
3 minutes before I can see anything!!!
I've the same problem with some client applications, a simple query can take
a long time, and another time (5minutes) the same query takes only 5 seconds
after a reboot.
If I restart my server, then all works fine, but when my SQL Server instance
start to consume more memory, then the performance slow down. I don't
understand why, because I've some memory available.
What can I monitor to identify the problem?
Thanks for your guides.
Jrme.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09What is the processor utilization?... Sounds like you've got something
eating it all up!
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:utqpL3V9DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've some performance problems on a server.
> I monitor these statistics:
> Pages/sec
> page faults/sec
> available MBytes
> the result is strange during small activities on the server: (min, avg,
max)
> Pages/sec = 0, 30, 217
> page faults/sec = 18, 350, 4000
> available MBytes = 290Mb, 300,Mb 308Mb
> My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
> 1280Mb installed.
> Opening the property page of SQL server to manage the memory could take 2
to
> 3 minutes before I can see anything!!!
> I've the same problem with some client applications, a simple query can
take
> a long time, and another time (5minutes) the same query takes only 5
seconds
> after a reboot.
> If I restart my server, then all works fine, but when my SQL Server
instance
> start to consume more memory, then the performance slow down. I don't
> understand why, because I've some memory available.
> What can I monitor to identify the problem?
> Thanks for your guides.
> Jrme.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09
>
Performance problem but memory available...
Hi,
I've some performance problems on a server.
I monitor these statistics:
Pages/sec
page faults/sec
available MBytes
the result is strange during small activities on the server: (min, avg, max)
Pages/sec = 0, 30, 217
page faults/sec = 18, 350, 4000
available MBytes = 290Mb, 300,Mb 308Mb
My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
1280Mb installed.
Opening the property page of SQL server to manage the memory could take 2 to
3 minutes before I can see anything!!!
I've the same problem with some client applications, a simple query can take
a long time, and another time (5minutes) the same query takes only 5 seconds
after a reboot.
If I restart my server, then all works fine, but when my SQL Server instance
start to consume more memory, then the performance slow down. I don't
understand why, because I've some memory available.
What can I monitor to identify the problem?
Thanks for your guides.
Jérôme.
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09What is the processor utilization?... Sounds like you've got something
eating it all up!
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:utqpL3V9DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've some performance problems on a server.
> I monitor these statistics:
> Pages/sec
> page faults/sec
> available MBytes
> the result is strange during small activities on the server: (min, avg,
max)
> Pages/sec = 0, 30, 217
> page faults/sec = 18, 350, 4000
> available MBytes = 290Mb, 300,Mb 308Mb
> My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
> 1280Mb installed.
> Opening the property page of SQL server to manage the memory could take 2
to
> 3 minutes before I can see anything!!!
> I've the same problem with some client applications, a simple query can
take
> a long time, and another time (5minutes) the same query takes only 5
seconds
> after a reboot.
> If I restart my server, then all works fine, but when my SQL Server
instance
> start to consume more memory, then the performance slow down. I don't
> understand why, because I've some memory available.
> What can I monitor to identify the problem?
> Thanks for your guides.
> Jérôme.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09
>
I've some performance problems on a server.
I monitor these statistics:
Pages/sec
page faults/sec
available MBytes
the result is strange during small activities on the server: (min, avg, max)
Pages/sec = 0, 30, 217
page faults/sec = 18, 350, 4000
available MBytes = 290Mb, 300,Mb 308Mb
My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
1280Mb installed.
Opening the property page of SQL server to manage the memory could take 2 to
3 minutes before I can see anything!!!
I've the same problem with some client applications, a simple query can take
a long time, and another time (5minutes) the same query takes only 5 seconds
after a reboot.
If I restart my server, then all works fine, but when my SQL Server instance
start to consume more memory, then the performance slow down. I don't
understand why, because I've some memory available.
What can I monitor to identify the problem?
Thanks for your guides.
Jérôme.
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09What is the processor utilization?... Sounds like you've got something
eating it all up!
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:utqpL3V9DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've some performance problems on a server.
> I monitor these statistics:
> Pages/sec
> page faults/sec
> available MBytes
> the result is strange during small activities on the server: (min, avg,
max)
> Pages/sec = 0, 30, 217
> page faults/sec = 18, 350, 4000
> available MBytes = 290Mb, 300,Mb 308Mb
> My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
> 1280Mb installed.
> Opening the property page of SQL server to manage the memory could take 2
to
> 3 minutes before I can see anything!!!
> I've the same problem with some client applications, a simple query can
take
> a long time, and another time (5minutes) the same query takes only 5
seconds
> after a reboot.
> If I restart my server, then all works fine, but when my SQL Server
instance
> start to consume more memory, then the performance slow down. I don't
> understand why, because I've some memory available.
> What can I monitor to identify the problem?
> Thanks for your guides.
> Jérôme.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09
>
Saturday, February 25, 2012
performance monitors on the SQL 2005
Looking to see if there is anyway to enable or capture or if already
available query performance stats on SQL2005
we are having issues with one of our databases.. and the programmers say
everything is ok.. ( they are outside verndors)
So is there anything we can get from SQL2005 to show us the times the
performance was low or high.. and such...
--
ASP, SQL2005, DW8 VBScriptTake a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||You can gleen this info from sys.dm_exec_query_stats with some limitations.
select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||Daniel
http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
available query performance stats on SQL2005
we are having issues with one of our databases.. and the programmers say
everything is ok.. ( they are outside verndors)
So is there anything we can get from SQL2005 to show us the times the
performance was low or high.. and such...
--
ASP, SQL2005, DW8 VBScriptTake a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||You can gleen this info from sys.dm_exec_query_stats with some limitations.
select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||Daniel
http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
Subscribe to:
Posts (Atom)