Performance Tuning Class on SQL2005

Does anyone know a class that concentrate or worth taking
which cover advanced performance tuning on SQL2005?
LitoI doubt such exists yet. You might want to check the Solid Quality Learning
class for SQL Server
2000 (see my signature). Vast majority applies for 2005 same way as for 2000
, where 2005 adds some
stuff to the picture.
Of course, if you already know your 2000 perf tuning stuff and are only look
ing for 2005 news, I
suggest you wait a while until things has matured.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lito D" <anonymous@.discussions.microsoft.com> wrote in message
news:136a01c5405d$16bb3170$a501280a@.phx.gbl...
> Does anyone know a class that concentrate or worth taking
> which cover advanced performance tuning on SQL2005?
> Lito

Performance Tuning Class on SQL2005

Does anyone know a class that concentrate or worth taking
which cover advanced performance tuning on SQL2005?
Lito
I doubt such exists yet. You might want to check the Solid Quality Learning class for SQL Server
2000 (see my signature). Vast majority applies for 2005 same way as for 2000, where 2005 adds some
stuff to the picture.
Of course, if you already know your 2000 perf tuning stuff and are only looking for 2005 news, I
suggest you wait a while until things has matured.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lito D" <anonymous@.discussions.microsoft.com> wrote in message
news:136a01c5405d$16bb3170$a501280a@.phx.gbl...
> Does anyone know a class that concentrate or worth taking
> which cover advanced performance tuning on SQL2005?
> Lito
sql

Performance Tuning Class on SQL2005

Does anyone know a class that concentrate or worth taking
which cover advanced performance tuning on SQL2005?
LitoI doubt such exists yet. You might want to check the Solid Quality Learning class for SQL Server
2000 (see my signature). Vast majority applies for 2005 same way as for 2000, where 2005 adds some
stuff to the picture.
Of course, if you already know your 2000 perf tuning stuff and are only looking for 2005 news, I
suggest you wait a while until things has matured.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lito D" <anonymous@.discussions.microsoft.com> wrote in message
news:136a01c5405d$16bb3170$a501280a@.phx.gbl...
> Does anyone know a class that concentrate or worth taking
> which cover advanced performance tuning on SQL2005?
> Lito

Performance really poor if calling data from sql clients

Hello,
I have installed SQL2005 developer edition on a laptop (2 GB RAM), VS2005 on
another computer. The database is quite small (nearly no data or some test
data). The performance is still really bad:
xxx each time you open the server explorer in VS2005 and you use "show table
data" (takes at least 20 sec., sometimes time out).
xxx each time I read from tables within my application (I'm not reading the
full table, I'm selecting the data, number of records < 5)
What can be done in order to determine the performance consuming
element/object? The network the computer are attached consists of less than 5
users (1 GBit backbone).
Are there any configuration aspects which can help to improove the
performance?
Network pack size = 4096
Max. number of concurrent connections = 0
Boost SQL server priority is not checked
Max. server memory = 2 GB
Thanks,
Stephan
I can't vouch for any of the VS components when it comes to performance
since I don't use them for database access. But you should be able to use
profiler to see what is happening on the sql server end. Also what does the
query plan look like? Is it optimal? Did you run sp_updatestats?
Andrew J. Kelly SQL MVP
"Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
> Hello,
> I have installed SQL2005 developer edition on a laptop (2 GB RAM), VS2005
> on
> another computer. The database is quite small (nearly no data or some test
> data). The performance is still really bad:
> xxx each time you open the server explorer in VS2005 and you use "show
> table
> data" (takes at least 20 sec., sometimes time out).
> xxx each time I read from tables within my application (I'm not reading
> the
> full table, I'm selecting the data, number of records < 5)
> What can be done in order to determine the performance consuming
> element/object? The network the computer are attached consists of less
> than 5
> users (1 GBit backbone).
> Are there any configuration aspects which can help to improove the
> performance?
> Network pack size = 4096
> Max. number of concurrent connections = 0
> Boost SQL server priority is not checked
> Max. server memory = 2 GB
> Thanks,
> Stephan
|||Hello Andrew,
good hint with the profiler.
You know I'm "fighting" since a while to follow the documented
recommendation of using TCP instead of Named Pipes to get performance.
I see now if I using Named Pipes connection in VS2005, everything is going
faster in VS2005 (and I assume this will also happen for my application if I
do the same).
So, what is the problem with TCP as connection type? Already known issues by
Microsoft SQL developer teams?
Yours
Stephan
"Andrew J. Kelly" wrote:

> I can't vouch for any of the VS components when it comes to performance
> since I don't use them for database access. But you should be able to use
> profiler to see what is happening on the sql server end. Also what does the
> query plan look like? Is it optimal? Did you run sp_updatestats?
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
>
>
|||Ok, the performance is not stable:
it is true that with named pipes. it is going a bit faster, but each time I
select another table to be displayed, it is still waiting for something I
cannot see even in the profiler. There is an overhead I can't actually find
out.
Any idea?
Yours
Stephan
"Stephan (Germany)" wrote:
[vbcol=seagreen]
> Hello Andrew,
> good hint with the profiler.
> You know I'm "fighting" since a while to follow the documented
> recommendation of using TCP instead of Named Pipes to get performance.
> I see now if I using Named Pipes connection in VS2005, everything is going
> faster in VS2005 (and I assume this will also happen for my application if I
> do the same).
> So, what is the problem with TCP as connection type? Already known issues by
> Microsoft SQL developer teams?
> Yours
> Stephan
> "Andrew J. Kelly" wrote:
|||Running of sp_updatestats fails. Error: stats_ver_current is not a built-in
function.
"Andrew J. Kelly" wrote:

> I can't vouch for any of the VS components when it comes to performance
> since I don't use them for database access. But you should be able to use
> profiler to see what is happening on the sql server end. Also what does the
> query plan look like? Is it optimal? Did you run sp_updatestats?
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
>
>
|||What is the compatibility level of the db? If it was a db that was upgraded
it may be in 80 compatibility mode.
Andrew J. Kelly SQL MVP
"Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...[vbcol=seagreen]
> Running of sp_updatestats fails. Error: stats_ver_current is not a
> built-in
> function.
> "Andrew J. Kelly" wrote:
|||If you run the same query from SSIS on the server does it run fast or slow?
Sounds like network issues or problems with name resolution.
Andrew J. Kelly SQL MVP
"Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
message news:E1285720-7132-4B56-B754-B98E9594F466@.microsoft.com...[vbcol=seagreen]
> Ok, the performance is not stable:
> it is true that with named pipes. it is going a bit faster, but each time
> I
> select another table to be displayed, it is still waiting for something I
> cannot see even in the profiler. There is an overhead I can't actually
> find
> out.
> Any idea?
> Yours
> Stephan
> "Stephan (Germany)" wrote:
|||Hello Andrew,
what do you mean with SSIS ?
I have checked DNS, WINS and address resolution. The laptop where the
database is running can be found via ping and nslookup.
If I make another test (creating new logins in SQL management studio), it is
not possible to see any other domain rather than the laptop itself, this is a
bit confusing.
Maybe it has nothing to do with my problem but it is not really good.
"Andrew J. Kelly" wrote:

> If you run the same query from SSIS on the server does it run fast or slow?
> Sounds like network issues or problems with name resolution.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:E1285720-7132-4B56-B754-B98E9594F466@.microsoft.com...
>
>
|||The db was installed completely new. I have uninstalled SQL 2000 first.
But how can I find out finally which compatibility mode the SQL server is
running now? I see no property in management studio nor server configuration
manager.
Yours
Stephan
"Andrew J. Kelly" wrote:

> What is the compatibility level of the db? If it was a db that was upgraded
> it may be in 80 compatibility mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...
>
>
|||Maybe this can help you:
the most time consuming period (waiting time) will arise if I connect the
server. If I pause a while and send another SQL command, the reestablishing
of the connection (same connecting string) needs again a while and then I can
fire different sql commands which will be executed in an acceptable speed.
Yours
Stephan
"Andrew J. Kelly" wrote:

> What is the compatibility level of the db? If it was a db that was upgraded
> it may be in 80 compatibility mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...
>
>
sql

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
>