performance tuning on high volume server

I am looking to improve the performance of my sql server databases.

I currently have a dual location system, the database server setup is basically a quad xeon with 4gb at my office and a double xeon with 4gb at a remote webhosting location. There are separate application/web/intranet servers at each site. The two databases servers are replicated with the local server publishing to the remote server.

The relational database holds circa 26 million records, growing by a volume of 10,000 per day, there are approximately 50,000 queries performed per day.

My theory is that the replication of the two databases is causing a slowdown; despite fast network connections (averaging 200ms between servers) the replication seems to place a large load on the local server. Would it be sensible to replicate to a second local server and then replicate to the remote server, placing any burden on the second server?

I am planning to upgrade the local server to a high capacity 4+ cpu 64bit server, my problem is that although I have noticed a slow down in performance over time, I am unsure how to go about measuring and quantifying this in order to diagnose the bottlenecks and ensure that investing in a new server would be worthwhile. Where would one be best advised to start this project?

Hi Gavin. What type of replication scheme have you implemented? Transactional/merge? Peer-to-peer, read-only subscriber, queud/immediate updating subscriber, etc.? Where to start on the research would have a lot to do with what type of topology you are using.

How are you getting to the conclusion that replication is responsible for the slowdown?

|||

Hi - it is a transactional type replication.

Chad Boyd MSFT wrote:

How are you getting to the conclusion that replication is responsible for the slowdown?

When turing off replication, the performance was improved. It seems that the bandwidth between servers would explain this.

What can I tell you about the topology?

|||

Hi Gavin. Is it a bi-directional replication setup? i.e. are the subscribers set to replicate updates back to the publisher, or are the subscribers simply read-only? What type of link exists between the sites (T1, T3, partial T, etc.)?

From the general sounds of things, you don't have an extremely busy write server, so a decent link between the 2 sites sounds sufficient for what you have, which is why it would be surprising to hear that the bottleneck is the network bandwidth...of course, it most certainly could be depending on the types of transactions you are seeing, this is just me thinking out loud.

You mentioned when turning off replication that performance improved, do you mean that end-users received responses to queries faster? Or you noticed particular counters drop significantly? Or possibly blocking/locking issues disipated?

I'd be surprised if the link is the bottleneck, since when you say performance improved I'm going to assume you mean end-users started seeing faster response times to requests...if that's the case, it would seem that there is something occuring on the box itself that is slowing down the response times (of course, that could be the replication agent keeping a lock on something because it is waiting for a response from the subscriber across a slow link, but in transactional replication, that's not as common as with merge, where the agents are querying tables directly...in transactional replication, the log is read directly).

Anything you can post that explains what you are seeing in terms of what is showing you performance is improved? Counters, query response times, etc.?

|||

I am also working with large volumes of data average of 12million records per table and a total of 23million record.No cluster or Indexes, this is because the data is to bulk to change.While running queries i find that my application hangs even if i set the ODBC timeout to 0. Unlike you i working with a normal X86 2.86 GHZ and 504MB RAM.

Please advice

|||

thank you for all your replies and assistance so far.

Chad - to answer your questions the subscribers are all read-only and there is a T1 link between sites.

As I am a developer and not a database specialist I have decided that I need to bring in some outsourced consultancy. Before I do this I would like to do some research so that I can learn as much as possible I would like to be up to speed on this and have as much background knowledge as possible.

I think that the first thing that I should do is to measure the facts as much as possible. Could you please advise me as to what tools and applications I can utilise to gather statistical facts?

What can I learn from my log files? What monitoring tools can I install?

performance tuning on high volume server

I am looking to improve the performance of my sql server databases.

I currently have a dual location system, the database server setup is basically a quad xeon with 4gb at my office and a double xeon with 4gb at a remote webhosting location. There are separate application/web/intranet servers at each site. The two databases servers are replicated with the local server publishing to the remote server.

The relational database holds circa 26 million records, growing by a volume of 10,000 per day, there are approximately 50,000 queries performed per day.

My theory is that the replication of the two databases is causing a slowdown; despite fast network connections (averaging 200ms between servers) the replication seems to place a large load on the local server. Would it be sensible to replicate to a second local server and then replicate to the remote server, placing any burden on the second server?

I am planning to upgrade the local server to a high capacity 4+ cpu 64bit server, my problem is that although I have noticed a slow down in performance over time, I am unsure how to go about measuring and quantifying this in order to diagnose the bottlenecks and ensure that investing in a new server would be worthwhile. Where would one be best advised to start this project?

Hi Gavin. What type of replication scheme have you implemented? Transactional/merge? Peer-to-peer, read-only subscriber, queud/immediate updating subscriber, etc.? Where to start on the research would have a lot to do with what type of topology you are using.

How are you getting to the conclusion that replication is responsible for the slowdown?

|||

Hi - it is a transactional type replication.

Chad Boyd MSFT wrote:

How are you getting to the conclusion that replication is responsible for the slowdown?

When turing off replication, the performance was improved. It seems that the bandwidth between servers would explain this.

What can I tell you about the topology?

|||

Hi Gavin. Is it a bi-directional replication setup? i.e. are the subscribers set to replicate updates back to the publisher, or are the subscribers simply read-only? What type of link exists between the sites (T1, T3, partial T, etc.)?

From the general sounds of things, you don't have an extremely busy write server, so a decent link between the 2 sites sounds sufficient for what you have, which is why it would be surprising to hear that the bottleneck is the network bandwidth...of course, it most certainly could be depending on the types of transactions you are seeing, this is just me thinking out loud.

You mentioned when turning off replication that performance improved, do you mean that end-users received responses to queries faster? Or you noticed particular counters drop significantly? Or possibly blocking/locking issues disipated?

I'd be surprised if the link is the bottleneck, since when you say performance improved I'm going to assume you mean end-users started seeing faster response times to requests...if that's the case, it would seem that there is something occuring on the box itself that is slowing down the response times (of course, that could be the replication agent keeping a lock on something because it is waiting for a response from the subscriber across a slow link, but in transactional replication, that's not as common as with merge, where the agents are querying tables directly...in transactional replication, the log is read directly).

Anything you can post that explains what you are seeing in terms of what is showing you performance is improved? Counters, query response times, etc.?

|||

I am also working with large volumes of data average of 12million records per table and a total of 23million record.No cluster or Indexes, this is because the data is to bulk to change.While running queries i find that my application hangs even if i set the ODBC timeout to 0. Unlike you i working with a normal X86 2.86 GHZ and 504MB RAM.

Please advice

|||

thank you for all your replies and assistance so far.

Chad - to answer your questions the subscribers are all read-only and there is a T1 link between sites.

As I am a developer and not a database specialist I have decided that I need to bring in some outsourced consultancy. Before I do this I would like to do some research so that I can learn as much as possible I would like to be up to speed on this and have as much background knowledge as possible.

I think that the first thing that I should do is to measure the facts as much as possible. Could you please advise me as to what tools and applications I can utilise to gather statistical facts?

What can I learn from my log files? What monitoring tools can I install?

performance tuning as a DBA for ERP system

Hi,
I have an ERP system running on SQL server 2000. How do i actually go abt
doing performance tuning as a DBA ?
i can check the no of ppl using the ERP and their activities. but come to
slow performance , how do i actually go abt doing performance tuning ?
for example , for this functional module of the ERP system i will not be
able to tell what stored procedures/queries that are being executed ? even if
i do know , i won't be able to change as that might cause further problem (do
i need to call the vendor support in this case ?) and my support agreement
will not cover any issues caused by changing the database ..
In the above scenario , how should a dba go abt perfoming a db performance
tunning ?
hope those experienced dbas can give me some ideas on how it works
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
You should be able to use profiler to watch for long running procs, or procs
which are doing a lot of reads or writes. These are candidates to be tuned.
Also watch for stored procedure recompilations, etc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:591855ccaf94c@.uwe...
> Hi,
> I have an ERP system running on SQL server 2000. How do i actually go abt
> doing performance tuning as a DBA ?
> i can check the no of ppl using the ERP and their activities. but come to
> slow performance , how do i actually go abt doing performance tuning ?
> for example , for this functional module of the ERP system i will not be
> able to tell what stored procedures/queries that are being executed ? even
> if
> i do know , i won't be able to change as that might cause further problem
> (do
> i need to call the vendor support in this case ?) and my support agreement
> will not cover any issues caused by changing the database ..
> In the above scenario , how should a dba go abt perfoming a db performance
> tunning ?
> hope those experienced dbas can give me some ideas on how it works
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200512/1
|||Hi,
would you recommend using a 3rd party s/w to monitor the SPs/Queries being
acccessed as i read somewhere that profiler is cpu-intensive and might "hang"
the SQL Server if running for a long period of time ?
tks & rdgs
Hilary Cotter wrote:[vbcol=seagreen]
>You should be able to use profiler to watch for long running procs, or procs
>which are doing a lot of reads or writes. These are candidates to be tuned.
>Also watch for stored procedure recompilations, etc.
>[quoted text clipped - 18 lines]
Message posted via http://www.droptable.com

Performance Reporting Services 2000

We are currently looking into to the performance of a system that utilizes
Reporting Services 2000. Can anyone point me to how to reduce response time
in generating reports? We generate 5 tabs of reports one for each business
unit and corp can see all of them. Is there any way to cache in 2000 based
on roles? The data is updated in the middle of the month and reviewed the
last week of the month. Is there a way to prime or execute these reports at
the beginning of the day with a scheduled type of job. Any help with
performance would be appreciated.instead of all of this bullshit caching talk; you should just be using
Analysis Services
much, much, much simpler architecture
-Aaron
Robert Bernheim wrote:
> We are currently looking into to the performance of a system that utilizes
> Reporting Services 2000. Can anyone point me to how to reduce response time
> in generating reports? We generate 5 tabs of reports one for each business
> unit and corp can see all of them. Is there any way to cache in 2000 based
> on roles? The data is updated in the middle of the month and reviewed the
> last week of the month. Is there a way to prime or execute these reports at
> the beginning of the day with a scheduled type of job. Any help with
> performance would be appreciated.|||In report manageer, under execution, set your report to generate from a
snapshot. and then set the snapshot schedule as needed, possibly monthly on
day 16. After updating make sure to click on Apply button at bottom of
execution screen. Also if you want an initial snapshot, check option <Create
a snapshot of the report when the apply button is selected>, apply changes
and you are done.
History snapshots can also be created manually by clicking on a report's
history tab at top of page and choosing <New Snapshot>.
Hope this helps.
"Robert Bernheim" wrote:
> We are currently looking into to the performance of a system that utilizes
> Reporting Services 2000. Can anyone point me to how to reduce response time
> in generating reports? We generate 5 tabs of reports one for each business
> unit and corp can see all of them. Is there any way to cache in 2000 based
> on roles? The data is updated in the middle of the month and reviewed the
> last week of the month. Is there a way to prime or execute these reports at
> the beginning of the day with a scheduled type of job. Any help with
> performance would be appreciated.
>

Performance question: Separate database or additional table?

We have a database that has about 50 tables, each with approximately 800K
rows. These tables are imports of data from another system that are updated
daily. All the tables have the same column, Id, for a primary key. The table
s
are accessed via views that serve mainly to assign meaningful names to the
columns and to insulate the applications from changes to the underlying
tables. In other words, the views are not restricting the users to a subset
of the columns in the tables, nor are they doing multi-table joins, etc. Thi
s
database is accessed by many users using many different applications across
the enterprise.
We have an application that queries a 12-view subset of the views, allowing
the user to do ad-hoc what-if queries with an application that generates SQL
queries from the user requests. The queries are often very time consuming. W
e
are attempting to improve the performance of this application. One thing we
know is that due to the nature of this application, 25% of the rows in the
views will never be selected and the application has to include SQL to
explicitly exlude this 25% of the rows.
One thought is to eliminate the scanning of the 25% of the rows that the
application never uses. There appear to be two ways of doing this:
1) Create another database containing only the 10-table/view subset and to
eliminate the unwanted rows at import/update time.
2) Create another table/view in the existing database with the Ids of the
75% of the rows that are used by this application and have the application d
o
a join on this table for all the queries and eliminate the SQL designed to
weed out the 25% of the rows that are not used by the application.
Whatever we choose to do, we have to live within the constraint of our
current hardware, so if we choose to create a separate database it will have
to share the current hardware with the other applications.
Given that the processing required to either load the alternate database or
maintain the 75% table is not an issue, which choice is likely to give us th
e
best increase in performance for the application?
Thanks,
BobHi
If you are currently seeing conflict between the two types of usage then it
is probably a good idea to create a second database for this to work on.
There will be a latency in the data unless depending on how you implement th
e
propogation of data.
I think you should look at the index usage on the database first, before you
implement any complex task to reduce the data.
John
"Bob" wrote:

> We have a database that has about 50 tables, each with approximately 800K
> rows. These tables are imports of data from another system that are update
d
> daily. All the tables have the same column, Id, for a primary key. The tab
les
> are accessed via views that serve mainly to assign meaningful names to the
> columns and to insulate the applications from changes to the underlying
> tables. In other words, the views are not restricting the users to a subse
t
> of the columns in the tables, nor are they doing multi-table joins, etc. T
his
> database is accessed by many users using many different applications acros
s
> the enterprise.
> We have an application that queries a 12-view subset of the views, allowin
g
> the user to do ad-hoc what-if queries with an application that generates S
QL
> queries from the user requests. The queries are often very time consuming.
We
> are attempting to improve the performance of this application. One thing w
e
> know is that due to the nature of this application, 25% of the rows in the
> views will never be selected and the application has to include SQL to
> explicitly exlude this 25% of the rows.
> One thought is to eliminate the scanning of the 25% of the rows that the
> application never uses. There appear to be two ways of doing this:
> 1) Create another database containing only the 10-table/view subset and to
> eliminate the unwanted rows at import/update time.
> 2) Create another table/view in the existing database with the Ids of the
> 75% of the rows that are used by this application and have the application
do
> a join on this table for all the queries and eliminate the SQL designed to
> weed out the 25% of the rows that are not used by the application.
> Whatever we choose to do, we have to live within the constraint of our
> current hardware, so if we choose to create a separate database it will ha
ve
> to share the current hardware with the other applications.
> Given that the processing required to either load the alternate database o
r
> maintain the 75% table is not an issue, which choice is likely to give us
the
> best increase in performance for the application?
> Thanks,
> Bob
>|||John,
"John Bell" wrote:

> Hi
> If you are currently seeing conflict between the two types of usage then
it
> is probably a good idea to create a second database for this to work on.
> There will be a latency in the data unless depending on how you implement
the
> propogation of data.
>
It's not really a conflict. The 75% part of the data represents currently
active items while the other 25% is effectively history. Items move from
current to history and new items are added, on a daily basis as the result o
f
batch processing on another system. This also eliminates data latency as an
issue.

> I think you should look at the index usage on the database first, before y
ou
> implement any complex task to reduce the data.
>
The column that determines whether an item is current or history is already
indexed. In addition, all the other columns used by the queries are also
indexed as appropriate.
Bob|||Assuming all other factors (indexing, joins, etc.) remain the same, I would
not expect removing 25% of the rows to impact the total runtime of the
queries that much. If 75% of the rows could be archived elsewhere, then that
would be significant. Rather than guessing, there are methods to know for
sure where the bottleneck is:
Investigate to what extent your indexes may be fragmented and defragment if
needed.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Use the Show Execution Plan feature in Query Analyzer to see exactly how the
query optimizer is using your indexes.
http://support.microsoft.com/defaul...;243589&sd=tech
SQL Server hardware configuration and monitoring memory usage, IO
performance, etc:
/url]
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx#EE
AA" target="_blank">http://www.microsoft.com/technet/pr...px#EE
AA
"Bob" <notrainsley@.worldsavings.com> wrote in message
news:B41801C0-B505-4A2C-9E3B-CF4D5E7C5C9A@.microsoft.com...
> We have a database that has about 50 tables, each with approximately 800K
> rows. These tables are imports of data from another system that are
> updated
> daily. All the tables have the same column, Id, for a primary key. The
> tables
> are accessed via views that serve mainly to assign meaningful names to the
> columns and to insulate the applications from changes to the underlying
> tables. In other words, the views are not restricting the users to a
> subset
> of the columns in the tables, nor are they doing multi-table joins, etc.
> This
> database is accessed by many users using many different applications
> across
> the enterprise.
> We have an application that queries a 12-view subset of the views,
> allowing
> the user to do ad-hoc what-if queries with an application that generates
> SQL
> queries from the user requests. The queries are often very time consuming.
> We
> are attempting to improve the performance of this application. One thing
> we
> know is that due to the nature of this application, 25% of the rows in the
> views will never be selected and the application has to include SQL to
> explicitly exlude this 25% of the rows.
> One thought is to eliminate the scanning of the 25% of the rows that the
> application never uses. There appear to be two ways of doing this:
> 1) Create another database containing only the 10-table/view subset and to
> eliminate the unwanted rows at import/update time.
> 2) Create another table/view in the existing database with the Ids of the
> 75% of the rows that are used by this application and have the application
> do
> a join on this table for all the queries and eliminate the SQL designed to
> weed out the 25% of the rows that are not used by the application.
> Whatever we choose to do, we have to live within the constraint of our
> current hardware, so if we choose to create a separate database it will
> have
> to share the current hardware with the other applications.
> Given that the processing required to either load the alternate database
> or
> maintain the 75% table is not an issue, which choice is likely to give us
> the
> best increase in performance for the application?
> Thanks,
> Bob
>|||Hi
JT gave you a link regarding the query plans. Just because your archive flag
is indexed does not necessarily mean it is used or if there is a better inde
x
configuration. With ad-hoc queries you may not get brilliant query plans all
of the time, but you may be able to produce an indexing strategy that gives
very good responses most of the time. It could be that with changed indexes
there is no need to separate the system. If the system is only updated by a
batch job, then you can use a strategy where you drop the indexes before
inserting the new data and re-creating the indexes after. If you don't drop
the indexes they should be rebuilt after the bulk load anyhow.
You may want to read some of the article on
http://www.sql-server-performance.c...performance.asp regarding how
to improve performance.
One thing that I don't think has been mentioned is that you may gain some
benefit from having indexed views.
John
"Bob" wrote:

> John,
> "John Bell" wrote:
>
> It's not really a conflict. The 75% part of the data represents currently
> active items while the other 25% is effectively history. Items move from
> current to history and new items are added, on a daily basis as the result
of
> batch processing on another system. This also eliminates data latency as a
n
> issue.
>
> The column that determines whether an item is current or history is alread
y
> indexed. In addition, all the other columns used by the queries are also
> indexed as appropriate.
> Bob
>|||JT and John,
Thanks for your replies. One of the problems I had was trying to actually
grab some of the generated queries to check out the execution plan in Query
Analyzer.
I got one of the DBAs to do a trace on the processing and we grabbed a few
of the generated queries. We discovered that there was a view someone had
written that wasn't part of the standard load and was doing massive amounts
of calculations.
I'm going to write a procedure that will use that view to create another
table loaded with the results of the calculations that will run as part of
the nightly load. The view will then simply be returning data instead of
calculating the values over and over. This will change a series of repetitiv
e
compute intensive calculations into a simple inner join on a primary key.
Thanks again for everyone's help.
Bob

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
.See my reply to your previous post from this morning.
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc6001c43e8b$7d505750$a301280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
> .
>

Performance problems

Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.When this happens, did you try running the query manually from Query
Analyzer and see if the execution plan is any different, compared to the one
when everything was okay? It could be that the statistics changed or not
up-to-date. Do you recall any data changes that correspond with the times of
bad performance?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.|||"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> This leads us to believe that we have a query, an application or some
> other unknown that prevents sql server from escalating a row level lock to
> table level.
I had this ince, by a simple DTS table export, got _millions_ of locks.
Defragmented the database in question, and it helped, operation done exactly
the same way suddenly went without any problems.
Seems like some hard-to-reproduce SQL Server problem. I monitor the lock
count closely since then, and defragmenting is my first resort in such a
case, but it never happened again since then.
Hope it helps.
Regards
Wojtek|||Hi
Locks are managed by memory. Have you checked how much memory SQL Server
consumes?
Also make sure that you don't have along running transactions. Have you
checked that your tables have propely defined indexes and moreover an
optimizer is able to use them when it creates an execution plan.
Try to identify by DBCC INPUTBUFFER (spid) what query is running by this
spid.
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi.
> We have a system with approximately 1000 users running on a dual Pentium
> with hyperthreading and 2Gb memory. The server is ultra fast and is
> performing great - most of the time. The problem is that sometimes a query
> that usually takes 2 seconds to run, suddenly will take 40 seconds or
more.
> It's like the system clutters up and everything becomes very slow.
> We have been running a profiler on the system, and found several stored
> procedures that needed optimizing and have done so - but we are still
> experiencing the same problem. We have looked at sp_lock and found that
most
> of the time there are about 200 locks but suddenly the number of locks
will
> increase to as much as 200.000 mostly on one table and for one spid.
> This leads us to believe that we have a query, an application or some
other
> unknown that prevents sql server from escalating a row level lock to table
> level.
> How do we find out if this is the case - is profiler the only way to go or
> are we missing something here? Any ideas or similar experiences?
> Your help will be greatly appreciated.
>

Performance problems

Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.
When this happens, did you try running the query manually from Query
Analyzer and see if the execution plan is any different, compared to the one
when everything was okay? It could be that the statistics changed or not
up-to-date. Do you recall any data changes that correspond with the times of
bad performance?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.
|||"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> This leads us to believe that we have a query, an application or some
> other unknown that prevents sql server from escalating a row level lock to
> table level.
I had this ince, by a simple DTS table export, got _millions_ of locks.
Defragmented the database in question, and it helped, operation done exactly
the same way suddenly went without any problems.
Seems like some hard-to-reproduce SQL Server problem. I monitor the lock
count closely since then, and defragmenting is my first resort in such a
case, but it never happened again since then.
Hope it helps.
Regards
Wojtek
|||Hi
Locks are managed by memory. Have you checked how much memory SQL Server
consumes?
Also make sure that you don't have along running transactions. Have you
checked that your tables have propely defined indexes and moreover an
optimizer is able to use them when it creates an execution plan.
Try to identify by DBCC INPUTBUFFER (spid) what query is running by this
spid.
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi.
> We have a system with approximately 1000 users running on a dual Pentium
> with hyperthreading and 2Gb memory. The server is ultra fast and is
> performing great - most of the time. The problem is that sometimes a query
> that usually takes 2 seconds to run, suddenly will take 40 seconds or
more.
> It's like the system clutters up and everything becomes very slow.
> We have been running a profiler on the system, and found several stored
> procedures that needed optimizing and have done so - but we are still
> experiencing the same problem. We have looked at sp_lock and found that
most
> of the time there are about 200 locks but suddenly the number of locks
will
> increase to as much as 200.000 mostly on one table and for one spid.
> This leads us to believe that we have a query, an application or some
other
> unknown that prevents sql server from escalating a row level lock to table
> level.
> How do we find out if this is the case - is profiler the only way to go or
> are we missing something here? Any ideas or similar experiences?
> Your help will be greatly appreciated.
>

Performance problems

Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.When this happens, did you try running the query manually from Query
Analyzer and see if the execution plan is any different, compared to the one
when everything was okay? It could be that the statistics changed or not
up-to-date. Do you recall any data changes that correspond with the times of
bad performance?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.|||"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> This leads us to believe that we have a query, an application or some
> other unknown that prevents sql server from escalating a row level lock to
> table level.
I had this ince, by a simple DTS table export, got _millions_ of locks.
Defragmented the database in question, and it helped, operation done exactly
the same way suddenly went without any problems.
Seems like some hard-to-reproduce SQL Server problem. I monitor the lock
count closely since then, and defragmenting is my first resort in such a
case, but it never happened again since then.
Hope it helps.
Regards
Wojtek|||Hi
Locks are managed by memory. Have you checked how much memory SQL Server
consumes?
Also make sure that you don't have along running transactions. Have you
checked that your tables have propely defined indexes and moreover an
optimizer is able to use them when it creates an execution plan.
Try to identify by DBCC INPUTBUFFER (spid) what query is running by this
spid.
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi.
> We have a system with approximately 1000 users running on a dual Pentium
> with hyperthreading and 2Gb memory. The server is ultra fast and is
> performing great - most of the time. The problem is that sometimes a query
> that usually takes 2 seconds to run, suddenly will take 40 seconds or
more.
> It's like the system clutters up and everything becomes very slow.
> We have been running a profiler on the system, and found several stored
> procedures that needed optimizing and have done so - but we are still
> experiencing the same problem. We have looked at sp_lock and found that
most
> of the time there are about 200 locks but suddenly the number of locks
will
> increase to as much as 200.000 mostly on one table and for one spid.
> This leads us to believe that we have a query, an application or some
other
> unknown that prevents sql server from escalating a row level lock to table
> level.
> How do we find out if this is the case - is profiler the only way to go or
> are we missing something here? Any ideas or similar experiences?
> Your help will be greatly appreciated.
>

Performance of XML vs Database

What is the performance comparison for XML and database?

Using system.IO to read the XML file will be slower than reading data from database , if only read data and not sorting?

The RAM/CPU memory usange will be higher for get data from XML compare to get data from database?

Hi,

You cant say that database is better than XML, its upto the scope of your need,the size of your XML and of much operations you are doing on XML file..

you can use DOM to read and if it is small file just load it at the start and maintain that till the end of the application..

Cheers

|||

happychai:

What is the performance comparison for XML and database?

It depends on the structure of the data. If it is a small file with something like a parent node and a child node with some attributes, and nothing that is relational then XML can be faster.

You will load the XML document once into the DOM and can you XPath to query the data very efficiently.

If you are going to edit data or the data structure is relational that always go for a data base.

|||

thanks for the reply, beside this i want to know is the performance on resouces use.

Is it using system.IO to read the XML using more CPU/Ram resouces than database?

|||

happychai:

Is it using system.IO to read the XML using more CPU/Ram resouces than database?

It all depends on the stucture of the XML and the query involved.

If the file is small and you are only doing a simple parse/xsl then the XML file should be faster but only neglible once the document is loaded into the DOM. Though you should not be basing on performance as a indicator for using XML or a database. With connection pooling and light weight data providers/objects, databases are also very quick.

|||

ok...

thanks for your reply

Performance of Views

I'm currently using a system where the number of column in any given table is so great that the columns are often split into additonal tables. I know it's a wierd design but there you go. So I have to deal with tables looking like:
MathResult, MathResult_2, MathResult_3, etc
Each table is basically the same entity, i.e. it has the same number of rows and each row has the same key value as its peer tables.
My question is that should I create a view to bring the tables together, given that a View doesn't seem to have any sort of row-size restriction? Normally I shy away from Views because I've always found them to bring performance down. Any thoughts?EDIT
Views are query rewrites so you will get mathematical problems like ISNULL function giving just plain wrong numbers. That said you can combine all into a UNION ALL view. This article is old but read what RDBMS(relational database management system) vendor agnostic expert Craig Mullins says about SQL Server Views in 1999.
I forgot to add that views don't use any query optimization plans and usually ignore the plans even if you create one. The reason persisted queries. Hope this helps.
http://www.craigsmullins.com/cnr_0299b.htm|||Thanks for the reply, yes the artical is a bit out-dated but IMO the basic points are still true. The trouble I'm having is that I know the views will be bad but I'm finding it difficult to quantify it, "how much worse will a view be"?|||

I would think that indexed Views will give you acceptable performance but I have not run the tests so I cannot be sure. The person who run some tests recently for all RDBMS(relational database management systems) for an advanced SQL book was Peter Gulutzan of now MySQL. I have the link to his recent article and a SQL Server specific Views performance article both should give you the dos and don't. Hope this helps.
http://www.dbazine.com/db2/db2-disarticles/gulutzan9

http://www.informit.com/articles/article.asp?p=130855

|||

Om Sri Sai Ram

I think indexed views effects performance. For every row insertion in base table, one record needs to be inserted for indexed view

Thanks,

Ram

Performance of system

Hi,
I am developing an editorial system(MS-SQL). There are a public part(frontend for reader) and an editorial part (backend for editor, admin,...).
The public part of system will be used approximately 1000 acesses/s.

A possible solution:
I solve whether I have to use two databases:
1.DB all data
2.DB with data for frontend

acesses............trigers
--->2.DB <----1.DB

or more of databases or else?

The purpose is, a reader should have good response that the reader don't wait.

Could you give me any advice ?
Thanksone database

make sure all tables are properly indexed for performance of your main queries|||and is there any at least one reason for more database ??|||separate database can be backed up and recovered separately

Performance of SPROC changed by dbo. prefix

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.

Any thoughts?

Edward<teddysnips@.hotmail.comwrote in message
news:1171898428.745133.216550@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.
>

dbo = database owner.

This is actually fairly common.

So not really sure what you're finding unusual here.

However, that said, you should call all stored procs with the owner
qualifier included.

Example:

stored proc FOO

Created by the sa so it's qualifed as:

dbo.FOO

Now user BAR comes along and calls:

exec FOO

First SQL Server will check to see if there is a stored proc BAR.FOO and try
to execute that. If not, THEN it'll look up dbo.FOO and try to execute
that.

Sounds like what's happening here. (Or something similar.) (note it's even
worse if it's named sp_xxxx).

Not sure why it would take 4-10 seconds, but I suspect that's part of the
issue.

Quote:

Originally Posted by

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.
>
Any thoughts?
>
Edward
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||teddysnips@.hotmail.com wrote:

Quote:

Originally Posted by

>
In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.
>
Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.
>
Any thoughts?
>
Edward

Maybe all the stored procedure needed was a recompilation. Maybe it had
nothing to do with the dbo prefix.

Please post back if the behavior is consistent (IOW, if performance
degrades if you change it back to the dbo prefix).

Gert-Jan|||(teddysnips@.hotmail.com) writes:

Quote:

Originally Posted by

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.
>
Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.
>
Any thoughts?

All stored procedures in a database (and all tables, all views etc)
belongs to a schema, and the full name within the database is
schema.procedure. If you leave out the schema when you create your
procedure, the procedure is created in your default schema. If you are
the database owner the default is "dbo". On SQL 2000, your default
schema is always the same as your user name. But in SQL 2005, owners
and schema are separeate, and all users can have dbo as their default
schema.

In many databases, all objects are in the dbo schema.

It follows from this, that whatever the performance problems with
your procedure due to, it was not the dbo prefix. (Unless you recreated
the procedure in your default schema which have tables that are
namesakes with those in the dbo schema - but are much smaller.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nlwrote in message
news:45DA261B.75D850BB@.toomuchspamalready.nl...

Quote:

Originally Posted by

teddysnips@.hotmail.com wrote:

Quote:

Originally Posted by

>>
>In a system I'm maintaining there is a Stored Procedure called
>dbo.MyStoredProcedure. I didn't create this - it was created by a
>developer who has now left. I don't know how the object came by its
>"dbo." prefix, but I think he created it in QA.
>>
>Anyway, there were some performance issues (it was taking between 4
>and 10 seconds to complete) so I copied the SQL into a QA window and
>it consistently ran in under 1 second. So I created a new SPROC with
>SQL exactly identical to the old one, but without the "dbo." prefix,
>and that too runs in <1 second.
>>
>Any thoughts?
>>
>Edward

>
Maybe all the stored procedure needed was a recompilation. Maybe it had
nothing to do with the dbo prefix.
>
Please post back if the behavior is consistent (IOW, if performance
degrades if you change it back to the dbo prefix).
>

Duh, didn't even think of recompilation.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Quote:

Originally Posted by

Gert-Jan

|||On 19 Feb, 22:35, Gert-Jan Strik <s...@.toomuchspamalready.nlwrote:

Quote:

Originally Posted by

teddysn...@.hotmail.com wrote:
>

Quote:

Originally Posted by

In a system I'm maintaining there is a Stored Procedure called
dbo.MyStoredProcedure. I didn't create this - it was created by a
developer who has now left. I don't know how the object came by its
"dbo." prefix, but I think he created it in QA.

>

Quote:

Originally Posted by

Anyway, there were some performance issues (it was taking between 4
and 10 seconds to complete) so I copied the SQL into a QA window and
it consistently ran in under 1 second. So I created a new SPROC with
SQL exactly identical to the old one, but without the "dbo." prefix,
and that too runs in <1 second.

>

Quote:

Originally Posted by

Any thoughts?

>

Quote:

Originally Posted by

Edward

>
Maybe all the stored procedure needed was a recompilation. Maybe it had
nothing to do with the dbo prefix.
>
Please post back if the behavior is consistent (IOW, if performance
degrades if you change it back to the dbo prefix).

10/10! I dropped the SPROC, then recreated it identically and it ran
like a greyhound. So all it needed was recompilation. I guess
there's an art to knowing how often/in what circumstances to recompile
SPROCs but I'm a developer, not a DBA so I don't know!

Thanks

Edward|||(teddysnips@.hotmail.com) writes:

Quote:

Originally Posted by

10/10! I dropped the SPROC, then recreated it identically and it ran
like a greyhound. So all it needed was recompilation. I guess
there's an art to knowing how often/in what circumstances to recompile
SPROCs but I'm a developer, not a DBA so I don't know!

You don't even have to drop the procedure, it sufficient to say:

sp_recompile procname

to flush all plans of it out the cache.

Normally, this is not needed, but SQL Server has this feature known as
parameter sniffing. When the optimizer builds the plan on the first
invocation, it looks at the actual parameter values and takes this as
guidance. But if that first invocation is for an untypical value, that
may buy you a plan which is bad for regular input.

This is not the only reason for a this sort of behaviour. It can also
be that the statistics are such that the optimizer's estimates for
two plans are very close, although one of the plans are not good at all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Performance Monitoring?

My SQL Server 2000 system is pegged. Disk activity is maxed out and system is very unresponsive. Several people have database tasks running through this system and I'm pretty sure there is a single application that is the culprit and I'd like to identify which one.

Does anyone have any practical tips on using "Process Info" in Enterprise Manager? What units are CPU and Physical I/O displayed in? Why does the column sort on these fields not work as expected?

Do I just pick the process with the largest Physical I/O and assume that's the problem?Use profiler...but I'd look for a high cpu and no I/O

That would be a process stuck in a loop, or a very labor intensive in memory process (less likely)

Although I did build 1 like that once...extremeley quick compared to the I/O version...took 1/2 hour...22 hours with I/O...

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related
. The third party system we have is archaic and code is written for SQL Serv
er 6.5. It has indices for multiple fields and all the data and index are in
one file. The size s almos
t 50 GB, most of them occupied by CHAR field without any valuable data. The
system created numerous table locks and one part of the system totally locks
out if a user is deleting from a table and other users are trying to access
the system (TABLOCK s). Se
arches take long time and system is crawling at peak time (2-3 pm) when we h
ave more than 1100 connections. Since its a third party tool we have limited
control over code change and they never do any support. The management is a
sking us for better results
. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices al
so into a file. Move the tempdb into another file and keep in a different ra
id array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related. The third party system we have is archaic and code is written for SQL Server 6.5. It has indices for multiple fields and all the data and index are in one file. The size s almos
t 50 GB, most of them occupied by CHAR field without any valuable data. The system created numerous table locks and one part of the system totally locks out if a user is deleting from a table and other users are trying to access the system (TABLOCK s). Se
arches take long time and system is crawling at peak time (2-3 pm) when we have more than 1100 connections. Since its a third party tool we have limited control over code change and they never do any support. The management is asking us for better results
. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices also into a file. Move the tempdb into another file and keep in a different raid array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related. The third party system we have is archaic and code is written for SQL Server 6.5. It has indices for multiple fields and all the data and index are in one file. The size s almost 50 GB, most of them occupied by CHAR field without any valuable data. The system created numerous table locks and one part of the system totally locks out if a user is deleting from a table and other users are trying to access the system (TABLOCK s). Searches take long time and system is crawling at peak time (2-3 pm) when we have more than 1100 connections. Since its a third party tool we have limited control over code change and they never do any support. The management is asking us for better results. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices also into a file. Move the tempdb into another file and keep in a different raid array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Performance monitor stats from table

Is there a way that instead of using performance monitory,
I can query from system tables to get information like
log flush wait time, Disk Read Queue length counter and
all related information.
Thanks.
Hi
Those are OS counters, and you need to run the OS Perfmon to capture those.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1119648499.019039.254950@.g43g2000cwa.googlegr oups.com...
> Is there a way that instead of using performance monitory,
> I can query from system tables to get information like
> log flush wait time, Disk Read Queue length counter and
> all related information.
> Thanks.
>

Performance monitor stats from table

Is there a way that instead of using performance monitory,
I can query from system tables to get information like
log flush wait time, Disk Read Queue length counter and
all related information.
Thanks.Hi
Those are OS counters, and you need to run the OS Perfmon to capture those.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1119648499.019039.254950@.g43g2000cwa.googlegroups.com...
> Is there a way that instead of using performance monitory,
> I can query from system tables to get information like
> log flush wait time, Disk Read Queue length counter and
> all related information.
> Thanks.
>

Performance monitor stats from table

Is there a way that instead of using performance monitory,
I can query from system tables to get information like
log flush wait time, Disk Read Queue length counter and
all related information.
Thanks.Hi
Those are OS counters, and you need to run the OS Perfmon to capture those.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1119648499.019039.254950@.g43g2000cwa.googlegroups.com...
> Is there a way that instead of using performance monitory,
> I can query from system tables to get information like
> log flush wait time, Disk Read Queue length counter and
> all related information.
> Thanks.
>