In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
Requests/Sec is around 2000 and the times for the execution of the INSERTS
and SELECTS are around 16MS which is perfomance for the client requests.
Suddenly, the Locks Request will drop to around 125 at the same time as the
INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
Is one causing the other?
Thanks,
Don
i've included the past thread below in case there's info that would help.
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are going
to 1 table while the majority are going to another table in the same DB. This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
Tom Moreau 2/13/2006 4:49 PM PST
Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
JXStern 2/13/2006 7:06 PM PST
On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT and
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.It could be lots of things but the two most common are blocking (not the
same as deadlocks) and checkpoints. You can see blocking by running sp_who2
and look for rows with a number (spid) in the Blkd column. But checkpoints
are a prime suspect as well. You can see when they occur by monitoring the
perfmon counters for CheckPoint Pages / Sec under the SQL Server counters.
When this happens the disks will usually max out for the duration of the
checkpoint and will stall any other requests to or from the disk. You stated
you have the data and log files separated onto separate disks but are they
just logical or really physical disks. Having the files on two logical
drives (partitions or LUNs) that are on the same physical drive array (or
worse single drive) does nothing for performance. It actually gives a false
sense of security. You can also have a look at these links to see how to
narrow down the culprit.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
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=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:02F1ED1B-AC28-4DA5-8CA1-E3735C6254B3@.microsoft.com...
> In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
> Requests/Sec is around 2000 and the times for the execution of the INSERTS
> and SELECTS are around 16MS which is perfomance for the client requests.
> Suddenly, the Locks Request will drop to around 125 at the same time as
> the
> INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
> Is one causing the other?
> Thanks,
> Don
> i've included the past thread below in case there's info that would help.
>
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> Tom Moreau 2/13/2006 4:49 PM PST
> Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
>
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> JXStern 2/13/2006 7:06 PM PST
> On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
> <donsql22222@.discussions.microsoft.com> wrote:
>>It's almost looks like theres some sort of contention between the INSERT
>>and
>>the SELECT, but since it's in separate tables...i have no idea what to
>>look
>>for.
> Of course there's contention, for LRU pages in memory, the moreso
> because they are different tables!
> Run the queries during the slow period from QA with SET STATISTICS IO
> ON, and look at the physical reads numbers going from 0 to whatever.
> J.sql
Showing posts with label selects. Show all posts
Showing posts with label selects. Show all posts
Wednesday, March 28, 2012
Lock Requests/Sec question
In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
Requests/Sec is around 2000 and the times for the execution of the INSERTS
and SELECTS are around 16MS which is perfomance for the client requests.
Suddenly, the Locks Request will drop to around 125 at the same time as the
INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
Is one causing the other?
Thanks,
Don
i've included the past thread below in case there's info that would help.
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist a
t
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are goin
g
to 1 table while the majority are going to another table in the same DB. Thi
s
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
Tom Moreau 2/13/2006 4:49 PM PST
Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
JXStern 2/13/2006 7:06 PM PST
On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT an
d
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.It could be lots of things but the two most common are blocking (not the
same as deadlocks) and checkpoints. You can see blocking by running sp_who2
and look for rows with a number (spid) in the Blkd column. But checkpoints
are a prime suspect as well. You can see when they occur by monitoring the
perfmon counters for CheckPoint Pages / Sec under the SQL Server counters.
When this happens the disks will usually max out for the duration of the
checkpoint and will stall any other requests to or from the disk. You stated
you have the data and log files separated onto separate disks but are they
just logical or really physical disks. Having the files on two logical
drives (partitions or LUNs) that are on the same physical drive array (or
worse single drive) does nothing for performance. It actually gives a false
sense of security. You can also have a look at these links to see how to
narrow down the culprit.
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...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=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:02F1ED1B-AC28-4DA5-8CA1-E3735C6254B3@.microsoft.com...
> In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
> Requests/Sec is around 2000 and the times for the execution of the INSERTS
> and SELECTS are around 16MS which is perfomance for the client requests.
> Suddenly, the Locks Request will drop to around 125 at the same time as
> the
> INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
> Is one causing the other?
> Thanks,
> Don
> i've included the past thread below in case there's info that would help.
>
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> Tom Moreau 2/13/2006 4:49 PM PST
> Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
>
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> JXStern 2/13/2006 7:06 PM PST
> On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
> <donsql22222@.discussions.microsoft.com> wrote:
> Of course there's contention, for LRU pages in memory, the moreso
> because they are different tables!
> Run the queries during the slow period from QA with SET STATISTICS IO
> ON, and look at the physical reads numbers going from 0 to whatever.
> J.
Requests/Sec is around 2000 and the times for the execution of the INSERTS
and SELECTS are around 16MS which is perfomance for the client requests.
Suddenly, the Locks Request will drop to around 125 at the same time as the
INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
Is one causing the other?
Thanks,
Don
i've included the past thread below in case there's info that would help.
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist a
t
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are goin
g
to 1 table while the majority are going to another table in the same DB. Thi
s
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
Tom Moreau 2/13/2006 4:49 PM PST
Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
JXStern 2/13/2006 7:06 PM PST
On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT an
d
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.It could be lots of things but the two most common are blocking (not the
same as deadlocks) and checkpoints. You can see blocking by running sp_who2
and look for rows with a number (spid) in the Blkd column. But checkpoints
are a prime suspect as well. You can see when they occur by monitoring the
perfmon counters for CheckPoint Pages / Sec under the SQL Server counters.
When this happens the disks will usually max out for the duration of the
checkpoint and will stall any other requests to or from the disk. You stated
you have the data and log files separated onto separate disks but are they
just logical or really physical disks. Having the files on two logical
drives (partitions or LUNs) that are on the same physical drive array (or
worse single drive) does nothing for performance. It actually gives a false
sense of security. You can also have a look at these links to see how to
narrow down the culprit.
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...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=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:02F1ED1B-AC28-4DA5-8CA1-E3735C6254B3@.microsoft.com...
> In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
> Requests/Sec is around 2000 and the times for the execution of the INSERTS
> and SELECTS are around 16MS which is perfomance for the client requests.
> Suddenly, the Locks Request will drop to around 125 at the same time as
> the
> INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
> Is one causing the other?
> Thanks,
> Don
> i've included the past thread below in case there's info that would help.
>
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> Tom Moreau 2/13/2006 4:49 PM PST
> Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
>
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> JXStern 2/13/2006 7:06 PM PST
> On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
> <donsql22222@.discussions.microsoft.com> wrote:
> Of course there's contention, for LRU pages in memory, the moreso
> because they are different tables!
> Run the queries during the slow period from QA with SET STATISTICS IO
> ON, and look at the physical reads numbers going from 0 to whatever.
> J.
Lock Requests/Sec question
In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
Requests/Sec is around 2000 and the times for the execution of the INSERTS
and SELECTS are around 16MS which is perfomance for the client requests.
Suddenly, the Locks Request will drop to around 125 at the same time as the
INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
Is one causing the other?
Thanks,
Don
i've included the past thread below in case there's info that would help.
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are going
to 1 table while the majority are going to another table in the same DB. This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
Tom Moreau 2/13/2006 4:49 PM PST
Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
...
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
JXStern 2/13/2006 7:06 PM PST
On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT and
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.
It could be lots of things but the two most common are blocking (not the
same as deadlocks) and checkpoints. You can see blocking by running sp_who2
and look for rows with a number (spid) in the Blkd column. But checkpoints
are a prime suspect as well. You can see when they occur by monitoring the
perfmon counters for CheckPoint Pages / Sec under the SQL Server counters.
When this happens the disks will usually max out for the duration of the
checkpoint and will stall any other requests to or from the disk. You stated
you have the data and log files separated onto separate disks but are they
just logical or really physical disks. Having the files on two logical
drives (partitions or LUNs) that are on the same physical drive array (or
worse single drive) does nothing for performance. It actually gives a false
sense of security. You can also have a look at these links to see how to
narrow down the culprit.
http://www.sql-server-performance.co...ce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pro...perations.mspx
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=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:02F1ED1B-AC28-4DA5-8CA1-E3735C6254B3@.microsoft.com...
> In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
> Requests/Sec is around 2000 and the times for the execution of the INSERTS
> and SELECTS are around 16MS which is perfomance for the client requests.
> Suddenly, the Locks Request will drop to around 125 at the same time as
> the
> INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
> Is one causing the other?
> Thanks,
> Don
> i've included the past thread below in case there's info that would help.
>
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> Tom Moreau 2/13/2006 4:49 PM PST
> Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
>
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> JXStern 2/13/2006 7:06 PM PST
> On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
> <donsql22222@.discussions.microsoft.com> wrote:
> Of course there's contention, for LRU pages in memory, the moreso
> because they are different tables!
> Run the queries during the slow period from QA with SET STATISTICS IO
> ON, and look at the physical reads numbers going from 0 to whatever.
> J.
Requests/Sec is around 2000 and the times for the execution of the INSERTS
and SELECTS are around 16MS which is perfomance for the client requests.
Suddenly, the Locks Request will drop to around 125 at the same time as the
INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
Is one causing the other?
Thanks,
Don
i've included the past thread below in case there's info that would help.
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are going
to 1 table while the majority are going to another table in the same DB. This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
Tom Moreau 2/13/2006 4:49 PM PST
Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
...
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
JXStern 2/13/2006 7:06 PM PST
On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT and
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.
It could be lots of things but the two most common are blocking (not the
same as deadlocks) and checkpoints. You can see blocking by running sp_who2
and look for rows with a number (spid) in the Blkd column. But checkpoints
are a prime suspect as well. You can see when they occur by monitoring the
perfmon counters for CheckPoint Pages / Sec under the SQL Server counters.
When this happens the disks will usually max out for the duration of the
checkpoint and will stall any other requests to or from the disk. You stated
you have the data and log files separated onto separate disks but are they
just logical or really physical disks. Having the files on two logical
drives (partitions or LUNs) that are on the same physical drive array (or
worse single drive) does nothing for performance. It actually gives a false
sense of security. You can also have a look at these links to see how to
narrow down the culprit.
http://www.sql-server-performance.co...ce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pro...perations.mspx
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=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:02F1ED1B-AC28-4DA5-8CA1-E3735C6254B3@.microsoft.com...
> In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
> Requests/Sec is around 2000 and the times for the execution of the INSERTS
> and SELECTS are around 16MS which is perfomance for the client requests.
> Suddenly, the Locks Request will drop to around 125 at the same time as
> the
> INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
> Is one causing the other?
> Thanks,
> Don
> i've included the past thread below in case there's info that would help.
>
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> Tom Moreau 2/13/2006 4:49 PM PST
> Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
>
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> JXStern 2/13/2006 7:06 PM PST
> On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
> <donsql22222@.discussions.microsoft.com> wrote:
> Of course there's contention, for LRU pages in memory, the moreso
> because they are different tables!
> Run the queries during the slow period from QA with SET STATISTICS IO
> ON, and look at the physical reads numbers going from 0 to whatever.
> J.
Friday, February 24, 2012
local database in c#
Hi,
I want to create a database in a path specified by the user at runtime when he selects a menu item like File/New. My preference is to not require the user to run an sql server on his machine to run my application. All my searches on the web for this keep directing me to the code that creates a database on an sql server but I don't want that. I want the application to create the database at runtime at a place where the user chooses. I would appreciate either a snippet of code or a link to somewhere helpful please.
Hi,
MS Access is the solution for u. It does not require any extra files to work. It just need MDAC to be installed and it's shipped with Windows.
All u have to do is to create ure database with all tables, views,... with no data. Ship the empty database with ure application and when the user wants to create a new database u just copy the empty database to the user location.
HTH.
Hayder Marzouk
|||Thanks Hayder.If there is a way to do this with a local sql database, I would appreciate some sample code to see how to do it.
|||
Hi,
You may create a T-SQL script and then run the script to create a database.
Move from C# express forum to Sql server forum.
Thanks
|||Not within your specified 'requirements'
My preference is to not require the user to run an sql server on his machine to run my application.
A 'Local' sql database will require that the user run or have access to a SQL Server.
As Hayder mentioned, an Access database is an excellent option and does not require that the user have access to, or install a SQL Server.
Subscribe to:
Posts (Atom)