The performance bottlenet is at the following query sent by the
Reconcile/Compuware third party application to SQL server
The most expensive step is on the sort step on the requ_tag column.
However if I put attv_value (text data type) to the last returning column,
the performance is OK.
Do anyone know why ?
How can I do from SQL server to improve performance without changing the
TSQL ?
(indexing ?)
SELECT RC_Requirements.requ_id,
RC_Requirement_type.reqt_id,
attv_value, -- text data type
requ_tag
FROM [RC_Requirements],
[RC_Requirement_type],
[RC_Attribute_value],
[RC_Attribute_definition]
WHERE RC_Requirement_type.reqt_id = RC_Requirements.reqt_id
AND RC_Attribute_value.requ_id = RC_Requirements.requ_id
AND RC_Attribute_value.attd_id = RC_Attribute_definition.attd_id
AND RC_Requirements.proj_id=43144
ORDER BY RC_Requirements.requ_tag"pc" <pc@.discussions.microsoft.com> wrote in message
news:25CB6A22-0DF5-4B18-A783-67F4846941D1@.microsoft.com...
> The performance bottlenet is at the following query sent by the
> Reconcile/Compuware third party application to SQL server
> The most expensive step is on the sort step on the requ_tag column.
> However if I put attv_value (text data type) to the last returning
> column,
> the performance is OK.
> Do anyone know why ?
> How can I do from SQL server to improve performance without changing the
> TSQL ?
> (indexing ?)
>
You can always try indexing requ_tag. This may avoid the expensive sort, as
the index will already maintain a sorted list of requ_tag.
David
Showing posts with label third. Show all posts
Showing posts with label third. Show all posts
Friday, March 9, 2012
performance on Reconcile/Compuware
The performance bottlenet is at the following query sent by the
Reconcile/Compuware third party application to SQL server
The most expensive step is on the sort step on the requ_tag column.
However if I put attv_value (text data type) to the last returning column,
the performance is OK.
Do anyone know why ?
How can I do from SQL server to improve performance without changing the
TSQL ?
(indexing ?)
SELECT RC_Requirements.requ_id,
RC_Requirement_type.reqt_id,
attv_value, -- text data type
requ_tag
FROM [RC_Requirements],
[RC_Requirement_type],
[RC_Attribute_value],
[RC_Attribute_definition]
WHERE RC_Requirement_type.reqt_id = RC_Requirements.reqt_id
AND RC_Attribute_value.requ_id = RC_Requirements.requ_id
AND RC_Attribute_value.attd_id = RC_Attribute_definition.attd_id
AND RC_Requirements.proj_id=43144
ORDER BY RC_Requirements.requ_tag
"pc" <pc@.discussions.microsoft.com> wrote in message
news:25CB6A22-0DF5-4B18-A783-67F4846941D1@.microsoft.com...
> The performance bottlenet is at the following query sent by the
> Reconcile/Compuware third party application to SQL server
> The most expensive step is on the sort step on the requ_tag column.
> However if I put attv_value (text data type) to the last returning
> column,
> the performance is OK.
> Do anyone know why ?
> How can I do from SQL server to improve performance without changing the
> TSQL ?
> (indexing ?)
>
You can always try indexing requ_tag. This may avoid the expensive sort, as
the index will already maintain a sorted list of requ_tag.
David
Reconcile/Compuware third party application to SQL server
The most expensive step is on the sort step on the requ_tag column.
However if I put attv_value (text data type) to the last returning column,
the performance is OK.
Do anyone know why ?
How can I do from SQL server to improve performance without changing the
TSQL ?
(indexing ?)
SELECT RC_Requirements.requ_id,
RC_Requirement_type.reqt_id,
attv_value, -- text data type
requ_tag
FROM [RC_Requirements],
[RC_Requirement_type],
[RC_Attribute_value],
[RC_Attribute_definition]
WHERE RC_Requirement_type.reqt_id = RC_Requirements.reqt_id
AND RC_Attribute_value.requ_id = RC_Requirements.requ_id
AND RC_Attribute_value.attd_id = RC_Attribute_definition.attd_id
AND RC_Requirements.proj_id=43144
ORDER BY RC_Requirements.requ_tag
"pc" <pc@.discussions.microsoft.com> wrote in message
news:25CB6A22-0DF5-4B18-A783-67F4846941D1@.microsoft.com...
> The performance bottlenet is at the following query sent by the
> Reconcile/Compuware third party application to SQL server
> The most expensive step is on the sort step on the requ_tag column.
> However if I put attv_value (text data type) to the last returning
> column,
> the performance is OK.
> Do anyone know why ?
> How can I do from SQL server to improve performance without changing the
> TSQL ?
> (indexing ?)
>
You can always try indexing requ_tag. This may avoid the expensive sort, as
the index will already maintain a sorted list of requ_tag.
David
Saturday, February 25, 2012
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.
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.
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.
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.
Subscribe to:
Posts (Atom)