It is possible, although I would not expect the clustering technology to =
have a noticable impact on performance. You talk about instances. Are =
you running multiple instances of SQL Server on the same hardware, or by =
"instance" do you mean Server A (standalone box) and Server B (clustered =
box)? If you are talking about multiple instances on one server are the =
instances configured to use the same amount of resources? =20
This reply will be posted within .server and it will also be copied to =
the .clustering newsgroup. Hopefully the experts that hang out there =
will have additional comments orideas.
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
>=20
> Consider the scenario ...
>=20
> Instance 1 of Sql Server running on Compaq with MSA1000=20
> disks=20
>=20
> Instance 2 of Sql Server running on Compaq with MSA1000=20
> disks BUT in a clustered environment
>=20
> We are finding that Instance 1 offers better performance=20
> than 2 for the same configuration (memory, sql version,=20
> windows etc). Could the use of cluster services for=20
> clustering be causing the overhead?
>=20
> TIA,
> Jack
>
I have never felt the presence of clustering on any of my SQL clusters. The
cluster service has such a small footprint. Could it be that you are not
comparing like DBs?
Cheers,
Rod
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OYr8Ip9TEHA.716@.TK2MSFTNGP11.phx.gbl...
It is possible, although I would not expect the clustering technology to
have a noticable impact on performance. You talk about instances. Are you
running multiple instances of SQL Server on the same hardware, or by
"instance" do you mean Server A (standalone box) and Server B (clustered
box)? If you are talking about multiple instances on one server are the
instances configured to use the same amount of resources?
This reply will be posted within .server and it will also be copied to the
..clustering newsgroup. Hopefully the experts that hang out there will have
additional comments orideas.
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
> Consider the scenario ...
> Instance 1 of Sql Server running on Compaq with MSA1000
> disks
> Instance 2 of Sql Server running on Compaq with MSA1000
> disks BUT in a clustered environment
> We are finding that Instance 1 offers better performance
> than 2 for the same configuration (memory, sql version,
> windows etc). Could the use of cluster services for
> clustering be causing the overhead?
> TIA,
> Jack
>
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
Cluster Indexes / Non-Cluster Indexes
I have application that I'm trying to enhance database performance.
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,
Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,
Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
Cluster Indexes / Non-Cluster Indexes
I have application that I'm trying to enhance database performance.
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
Cluster Indexes / Non-Cluster Indexes
I have application that I'm trying to enhance database performance.
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
--
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
--
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
Thursday, March 8, 2012
CLR Performance
We had a CLR function that was running in seconds under the April CTP - now it takes over a minute in the new Sep CTP. We've determined that the bottleneck is in the following line:
Dim SqlConn As SqlConnection = New SqlConnection("context connection=true")
Is there a new recommended way to open a connection?
That looks right to me, and I'd be very surprised if it was simply the SqlConnection constructor that was causing this (otherwise everyone would be hitting it).Can you provide any more information?
Thanks,
-Isaac|||
My fault - it's not the constructor that's the overhead, it's the open command. My test function looks like the following:
<SqlFunction(dataaccess:=DataAccessKind.Read)> _
Public Shared Function PerfTest() As Integer
Dim SqlConn As SqlConnection = New SqlConnection("context connection=true")
SqlConn.Open()
SqlConn.Close()
Return 0
End Function
If I call this function 15,000 times using Apr CTP, it runs in about 5 seconds. Using the latest, Sept CTP, it takes a minute or longer.
One other question: are you seeing this perf difference for functions specificially, or does it also show up for procedures?
~Alazel
Developer
Microsoft Sql Server
This posting is provided "AS IS" with no warranties, and confers no rights.|||- Is there anyway I can obtain the fix to try out?
- I do not see the view "sys.memory_clerks" that you mention in your mail.
- The problem is apparent in the function since I call it over and over in a SELECT statement. I'm not sure about procedures.
|||- Sept CTP is the last SQL Server 2005 CTP. We would RTM soon and the fix would be available in it.
- Its actually sys.dm_os_memory_clerks
- Could you try creating a Stored Procedure with the same code and execute it same number of times as suggested by Alazel?
Thanks,
-Vineet.|||- The single_pages_kb increased from 1088 to 1096 when running the function.
- I created a stored procedure as you requested and I see the same performance problem - it runs in about the same time as the function.
I'm just curious if you have tried to run the PerfTest function (listed earlier in the posting) with the new SQL Server version to make sure the fix you mentioned has truly fixed the problem.
|||I tested running your function 15,000 times on a post-CTP16 build and it executed quickly (2 seconds, actually) as both a UD Function and a UD Stored Procedure.
Here is how I executed it to make sure we're doing an equivalent test:
declare @.i int, @.null int
set @.i = 1
while @.i < 15000
begin
select @.null = dbo.PerfTest()
set @.i = @.i + 1
end|||Yes, you're sample is pretty much what I did to test the UD stored procedure. I just think it would be in Microsoft's interest to send me a patch to test the fix in my environment. Let me know.
CLR performance
Does CLR suffer from the same serialization performance overhead that
extended stored procedures do? Which is likely to execute faster? Thanks."Neil W" <neilw@.netlib.com> wrote in message
news:O3OeWyhmGHA.3880@.TK2MSFTNGP02.phx.gbl...
> Does CLR suffer from the same serialization performance overhead that
> extended stored procedures do? Which is likely to execute faster?
> Thanks.
What do you mean by "serialization performance overhead"?
David
extended stored procedures do? Which is likely to execute faster? Thanks."Neil W" <neilw@.netlib.com> wrote in message
news:O3OeWyhmGHA.3880@.TK2MSFTNGP02.phx.gbl...
> Does CLR suffer from the same serialization performance overhead that
> extended stored procedures do? Which is likely to execute faster?
> Thanks.
What do you mean by "serialization performance overhead"?
David
Saturday, February 25, 2012
close
Below, I have pasted a portion of an MS MSDN article dealing with a specific issue we are having with Report Server performance. The URL (http://support.microsoft.com/kb/821438/en-us) is the MS KB article on the described fix. The fix mentioned is for "ASP.NET 1.1" but our BP Report Server is using "ASP.NET 2.0".
Has anyone encountered and resolved and how?
Running on box:
ASP.NET 2.0.50727;
SSRS 2005 Sp1 + Hotfix#2175;
Win Server 2003, R2, x64, SP1
Trend Micro OfficeScan Cleint for Win 2003/xp v7.3
IIS Version (I cannot find this #) - ? IE 6.0
===========================
Report Manager or the report server runs very slowly
In some circumstances, ASP.NET applications run very slowly on computers that are running anti-virus software. If the Report Server Web service is restarting frequently, and you are running anti-virus software, you can obtain an ASP.NET fix from Microsoft Customer Support Services.
Symptoms include Web applications or Application Domains restarting for no apparent reason, slow performance, session restarts, and more. For more information about the symptoms, cause, and resolution, see Microsoft Knowledge Base article 821438.
You can find out whether there are excessive server restarts by viewing the number of reportserver_<timestamp>.log files. A new log is created each time the server starts. A large collection of logs created at very short intervals is an indication that the conditions described in article 821438 exist on your server
===========================
Friday, February 24, 2012
client/server performance problems
We have 1000 users on a client/server CRM application that we've written in
house. Recently we started experiencing performance problems at the top of
each hour, it lasts for about 30 seconds. I have run several traces using
these events:
lock: acquired,
lock: deadlock,
rpc: completed,
sql: stmtcompleted
Each trace runs from 10 till the hour until 10 after the hour. I've dumped
them out to tables so I could query them for the top 200 records by cpu,
duration, reads and writes. I then query them by each minute of starttime to
find the worst queries. I have fixed a lot of bad queries over the past
couple of weeks but still users are complaining. We are runnings Sql Server
2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for the
server but our I'm told it has a lot of cpu and at I think 8GB of memory. I
am not a dba, I am a developer but I cant understand why I'm not solving the
problem. The queries that I've fixed are running much faster, but still the
problem persists.
Am I not using the right events in SQL Profiler? Is it possible that we're
really having some sort of network issue at that time? I would use Perfmon
but that doesnt tell me much.
Any help is appreciated,
Doug
My guess is you have a scheduled job that runs on the hour each hour. It
could also be related to checkpoints but they rarely follow such an exact
cycle. You can see these with the SQL counter CHeckpoin Pages per second.
You should monitor for CPU, Disks etc as well. See if these help:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Doug Stiers" <doug@.nospam.com> wrote in message
news:%23t7lrXYCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have 1000 users on a client/server CRM application that we've written
> in
> house. Recently we started experiencing performance problems at the top of
> each hour, it lasts for about 30 seconds. I have run several traces using
> these events:
> lock: acquired,
> lock: deadlock,
> rpc: completed,
> sql: stmtcompleted
> Each trace runs from 10 till the hour until 10 after the hour. I've dumped
> them out to tables so I could query them for the top 200 records by cpu,
> duration, reads and writes. I then query them by each minute of starttime
> to
> find the worst queries. I have fixed a lot of bad queries over the past
> couple of weeks but still users are complaining. We are runnings Sql
> Server
> 2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for
> the
> server but our I'm told it has a lot of cpu and at I think 8GB of memory.
> I
> am not a dba, I am a developer but I cant understand why I'm not solving
> the
> problem. The queries that I've fixed are running much faster, but still
> the
> problem persists.
> Am I not using the right events in SQL Profiler? Is it possible that we're
> really having some sort of network issue at that time? I would use Perfmon
> but that doesnt tell me much.
> Any help is appreciated,
> Doug
>
house. Recently we started experiencing performance problems at the top of
each hour, it lasts for about 30 seconds. I have run several traces using
these events:
lock: acquired,
lock: deadlock,
rpc: completed,
sql: stmtcompleted
Each trace runs from 10 till the hour until 10 after the hour. I've dumped
them out to tables so I could query them for the top 200 records by cpu,
duration, reads and writes. I then query them by each minute of starttime to
find the worst queries. I have fixed a lot of bad queries over the past
couple of weeks but still users are complaining. We are runnings Sql Server
2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for the
server but our I'm told it has a lot of cpu and at I think 8GB of memory. I
am not a dba, I am a developer but I cant understand why I'm not solving the
problem. The queries that I've fixed are running much faster, but still the
problem persists.
Am I not using the right events in SQL Profiler? Is it possible that we're
really having some sort of network issue at that time? I would use Perfmon
but that doesnt tell me much.
Any help is appreciated,
Doug
My guess is you have a scheduled job that runs on the hour each hour. It
could also be related to checkpoints but they rarely follow such an exact
cycle. You can see these with the SQL counter CHeckpoin Pages per second.
You should monitor for CPU, Disks etc as well. See if these help:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Doug Stiers" <doug@.nospam.com> wrote in message
news:%23t7lrXYCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have 1000 users on a client/server CRM application that we've written
> in
> house. Recently we started experiencing performance problems at the top of
> each hour, it lasts for about 30 seconds. I have run several traces using
> these events:
> lock: acquired,
> lock: deadlock,
> rpc: completed,
> sql: stmtcompleted
> Each trace runs from 10 till the hour until 10 after the hour. I've dumped
> them out to tables so I could query them for the top 200 records by cpu,
> duration, reads and writes. I then query them by each minute of starttime
> to
> find the worst queries. I have fixed a lot of bad queries over the past
> couple of weeks but still users are complaining. We are runnings Sql
> Server
> 2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for
> the
> server but our I'm told it has a lot of cpu and at I think 8GB of memory.
> I
> am not a dba, I am a developer but I cant understand why I'm not solving
> the
> problem. The queries that I've fixed are running much faster, but still
> the
> problem persists.
> Am I not using the right events in SQL Profiler? Is it possible that we're
> really having some sort of network issue at that time? I would use Perfmon
> but that doesnt tell me much.
> Any help is appreciated,
> Doug
>
client/server performance problems
We have 1000 users on a client/server CRM application that we've written in
house. Recently we started experiencing performance problems at the top of
each hour, it lasts for about 30 seconds. I have run several traces using
these events:
lock: acquired,
lock: deadlock,
rpc: completed,
sql: stmtcompleted
Each trace runs from 10 till the hour until 10 after the hour. I've dumped
them out to tables so I could query them for the top 200 records by cpu,
duration, reads and writes. I then query them by each minute of starttime to
find the worst queries. I have fixed a lot of bad queries over the past
couple of weeks but still users are complaining. We are runnings Sql Server
2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for the
server but our I'm told it has a lot of cpu and at I think 8GB of memory. I
am not a dba, I am a developer but I cant understand why I'm not solving the
problem. The queries that I've fixed are running much faster, but still the
problem persists.
Am I not using the right events in SQL Profiler? Is it possible that we're
really having some sort of network issue at that time? I would use Perfmon
but that doesnt tell me much.
Any help is appreciated,
DougMy guess is you have a scheduled job that runs on the hour each hour. It
could also be related to checkpoints but they rarely follow such an exact
cycle. You can see these with the SQL counter CHeckpoin Pages per second.
You should monitor for CPU, Disks etc as well. See if these help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Doug Stiers" <doug@.nospam.com> wrote in message
news:%23t7lrXYCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have 1000 users on a client/server CRM application that we've written
> in
> house. Recently we started experiencing performance problems at the top of
> each hour, it lasts for about 30 seconds. I have run several traces using
> these events:
> lock: acquired,
> lock: deadlock,
> rpc: completed,
> sql: stmtcompleted
> Each trace runs from 10 till the hour until 10 after the hour. I've dumped
> them out to tables so I could query them for the top 200 records by cpu,
> duration, reads and writes. I then query them by each minute of starttime
> to
> find the worst queries. I have fixed a lot of bad queries over the past
> couple of weeks but still users are complaining. We are runnings Sql
> Server
> 2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for
> the
> server but our I'm told it has a lot of cpu and at I think 8GB of memory.
> I
> am not a dba, I am a developer but I cant understand why I'm not solving
> the
> problem. The queries that I've fixed are running much faster, but still
> the
> problem persists.
> Am I not using the right events in SQL Profiler? Is it possible that we're
> really having some sort of network issue at that time? I would use Perfmon
> but that doesnt tell me much.
> Any help is appreciated,
> Doug
>
house. Recently we started experiencing performance problems at the top of
each hour, it lasts for about 30 seconds. I have run several traces using
these events:
lock: acquired,
lock: deadlock,
rpc: completed,
sql: stmtcompleted
Each trace runs from 10 till the hour until 10 after the hour. I've dumped
them out to tables so I could query them for the top 200 records by cpu,
duration, reads and writes. I then query them by each minute of starttime to
find the worst queries. I have fixed a lot of bad queries over the past
couple of weeks but still users are complaining. We are runnings Sql Server
2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for the
server but our I'm told it has a lot of cpu and at I think 8GB of memory. I
am not a dba, I am a developer but I cant understand why I'm not solving the
problem. The queries that I've fixed are running much faster, but still the
problem persists.
Am I not using the right events in SQL Profiler? Is it possible that we're
really having some sort of network issue at that time? I would use Perfmon
but that doesnt tell me much.
Any help is appreciated,
DougMy guess is you have a scheduled job that runs on the hour each hour. It
could also be related to checkpoints but they rarely follow such an exact
cycle. You can see these with the SQL counter CHeckpoin Pages per second.
You should monitor for CPU, Disks etc as well. See if these help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Doug Stiers" <doug@.nospam.com> wrote in message
news:%23t7lrXYCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have 1000 users on a client/server CRM application that we've written
> in
> house. Recently we started experiencing performance problems at the top of
> each hour, it lasts for about 30 seconds. I have run several traces using
> these events:
> lock: acquired,
> lock: deadlock,
> rpc: completed,
> sql: stmtcompleted
> Each trace runs from 10 till the hour until 10 after the hour. I've dumped
> them out to tables so I could query them for the top 200 records by cpu,
> duration, reads and writes. I then query them by each minute of starttime
> to
> find the worst queries. I have fixed a lot of bad queries over the past
> couple of weeks but still users are complaining. We are runnings Sql
> Server
> 2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for
> the
> server but our I'm told it has a lot of cpu and at I think 8GB of memory.
> I
> am not a dba, I am a developer but I cant understand why I'm not solving
> the
> problem. The queries that I've fixed are running much faster, but still
> the
> problem persists.
> Am I not using the right events in SQL Profiler? Is it possible that we're
> really having some sort of network issue at that time? I would use Perfmon
> but that doesnt tell me much.
> Any help is appreciated,
> Doug
>
client/server performance problems
We have 1000 users on a client/server CRM application that we've written in
house. Recently we started experiencing performance problems at the top of
each hour, it lasts for about 30 seconds. I have run several traces using
these events:
lock: acquired,
lock: deadlock,
rpc: completed,
sql: stmtcompleted
Each trace runs from 10 till the hour until 10 after the hour. I've dumped
them out to tables so I could query them for the top 200 records by cpu,
duration, reads and writes. I then query them by each minute of starttime to
find the worst queries. I have fixed a lot of bad queries over the past
couple of weeks but still users are complaining. We are runnings Sql Server
2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for the
server but our I'm told it has a lot of cpu and at I think 8GB of memory. I
am not a dba, I am a developer but I cant understand why I'm not solving the
problem. The queries that I've fixed are running much faster, but still the
problem persists.
Am I not using the right events in SQL Profiler? Is it possible that we're
really having some sort of network issue at that time? I would use Perfmon
but that doesnt tell me much.
Any help is appreciated,
DougMy guess is you have a scheduled job that runs on the hour each hour. It
could also be related to checkpoints but they rarely follow such an exact
cycle. You can see these with the SQL counter CHeckpoin Pages per second.
You should monitor for CPU, Disks etc as well. See if these help:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Doug Stiers" <doug@.nospam.com> wrote in message
news:%23t7lrXYCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have 1000 users on a client/server CRM application that we've written
> in
> house. Recently we started experiencing performance problems at the top of
> each hour, it lasts for about 30 seconds. I have run several traces using
> these events:
> lock: acquired,
> lock: deadlock,
> rpc: completed,
> sql: stmtcompleted
> Each trace runs from 10 till the hour until 10 after the hour. I've dumped
> them out to tables so I could query them for the top 200 records by cpu,
> duration, reads and writes. I then query them by each minute of starttime
> to
> find the worst queries. I have fixed a lot of bad queries over the past
> couple of weeks but still users are complaining. We are runnings Sql
> Server
> 2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for
> the
> server but our I'm told it has a lot of cpu and at I think 8GB of memory.
> I
> am not a dba, I am a developer but I cant understand why I'm not solving
> the
> problem. The queries that I've fixed are running much faster, but still
> the
> problem persists.
> Am I not using the right events in SQL Profiler? Is it possible that we're
> really having some sort of network issue at that time? I would use Perfmon
> but that doesnt tell me much.
> Any help is appreciated,
> Doug
>
house. Recently we started experiencing performance problems at the top of
each hour, it lasts for about 30 seconds. I have run several traces using
these events:
lock: acquired,
lock: deadlock,
rpc: completed,
sql: stmtcompleted
Each trace runs from 10 till the hour until 10 after the hour. I've dumped
them out to tables so I could query them for the top 200 records by cpu,
duration, reads and writes. I then query them by each minute of starttime to
find the worst queries. I have fixed a lot of bad queries over the past
couple of weeks but still users are complaining. We are runnings Sql Server
2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for the
server but our I'm told it has a lot of cpu and at I think 8GB of memory. I
am not a dba, I am a developer but I cant understand why I'm not solving the
problem. The queries that I've fixed are running much faster, but still the
problem persists.
Am I not using the right events in SQL Profiler? Is it possible that we're
really having some sort of network issue at that time? I would use Perfmon
but that doesnt tell me much.
Any help is appreciated,
DougMy guess is you have a scheduled job that runs on the hour each hour. It
could also be related to checkpoints but they rarely follow such an exact
cycle. You can see these with the SQL counter CHeckpoin Pages per second.
You should monitor for CPU, Disks etc as well. See if these help:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Doug Stiers" <doug@.nospam.com> wrote in message
news:%23t7lrXYCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have 1000 users on a client/server CRM application that we've written
> in
> house. Recently we started experiencing performance problems at the top of
> each hour, it lasts for about 30 seconds. I have run several traces using
> these events:
> lock: acquired,
> lock: deadlock,
> rpc: completed,
> sql: stmtcompleted
> Each trace runs from 10 till the hour until 10 after the hour. I've dumped
> them out to tables so I could query them for the top 200 records by cpu,
> duration, reads and writes. I then query them by each minute of starttime
> to
> find the worst queries. I have fixed a lot of bad queries over the past
> couple of weeks but still users are complaining. We are runnings Sql
> Server
> 2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for
> the
> server but our I'm told it has a lot of cpu and at I think 8GB of memory.
> I
> am not a dba, I am a developer but I cant understand why I'm not solving
> the
> problem. The queries that I've fixed are running much faster, but still
> the
> problem persists.
> Am I not using the right events in SQL Profiler? Is it possible that we're
> really having some sort of network issue at that time? I would use Perfmon
> but that doesnt tell me much.
> Any help is appreciated,
> Doug
>
Subscribe to:
Posts (Atom)