I'm runing SQL2000 with SP4 on Win 2003 Ent Srvr and use a SAN for the
database data and log files.
The SAN drives are configured as RAID5.
My question is when diagnosing IO performance issues, what perfmon counters
are recommended for use when the databases are on a SAN? I used to rely on
current disk queue length but have been told that counter is skewed when the
storage is SAN. Also if other counters are recommended, what would be the
thresholds for judging poor performance? Any utilites recommended?
thksTom,
See if this helps.
http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx
AMB
"Tom Frost" wrote:
> I'm runing SQL2000 with SP4 on Win 2003 Ent Srvr and use a SAN for the
> database data and log files.
> The SAN drives are configured as RAID5.
> My question is when diagnosing IO performance issues, what perfmon counters
> are recommended for use when the databases are on a SAN? I used to rely on
> current disk queue length but have been told that counter is skewed when the
> storage is SAN. Also if other counters are recommended, what would be the
> thresholds for judging poor performance? Any utilites recommended?
> thks|||Hi Tom
"Tom Frost" wrote:
> I'm runing SQL2000 with SP4 on Win 2003 Ent Srvr and use a SAN for the
> database data and log files.
> The SAN drives are configured as RAID5.
> My question is when diagnosing IO performance issues, what perfmon counters
> are recommended for use when the databases are on a SAN? I used to rely on
> current disk queue length but have been told that counter is skewed when the
> storage is SAN. Also if other counters are recommended, what would be the
> thresholds for judging poor performance? Any utilites recommended?
> thks
Check out http://support.microsoft.com/kb/224587/
http://www.sql-server-performance.com/ew_san.asp
http://www.sql-server-performance.com/qdpma/inst_3_pmlogs.as
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
You SAN vendor should also have tools which you can monitor the performance,
this will be necessary to rule out interface issues i.e. the SAN is fine, but
the OS thinks it's slow!
John|||Hi,
I understand that you would like to know what performance counters should
be used to troubleshoot your SQL Server 2000 performance issue.
If I have misunderstood, please let me know.
I recommend that you refer to the following articles for monitoring your
SQL Server Performance:
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/kb/838622
Microsoft SQL Server 2000 RDBMS Performance Tuning Guide for Data
Warehousing
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
SQL Server 2000 I/O Basics (SQLIOStress.exe for stress test)
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.m
spx
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi,
Just check with you to see if the suggestions were helpful. Please let us
know if you would like further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
Showing posts with label configured. Show all posts
Showing posts with label configured. Show all posts
Friday, March 30, 2012
How to determine, inside a function, if a linked-server-query returned results
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@.Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @.SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @.Code + ''')'
EXEC sp_executesql @.SQL
(CAT_ASA is the linked-server's name)
Then, i would use @.@.ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!I never worked with an ASA6 db but how about using four-part naming instead of OpenQuery? In a normal query, you can use variables in your where clauses. So, if the column type of CODE is not something out of the ordinary and recognized by SQL Server, everything should run fine. There could be interface problems but usually with a query as simple as yours, it should work.
This is a simple solution that doesn't really answer your question. Consider it as a possible workaround.
Good luck,
Skip.|||Thanks for your answer, Skip. I also tried using a four part name, but SQL server gave me a message saying that the ODBC Interface doesnt support four-part names. I tried with a 3 part name (linkedservername.database.table), but it still doesnt works. The error was diferent (so, I supose that the names with this ODBC interface must have three parts). I read in another thread that the only way to make a query to a linked server was using OPENQUERY or OPENROWSET. Im not really sure about that, but i tried many ways using 3 or 4 part names and it never worked.|||In addition i tried something like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT code FROM COUNTRIES') WHERE code=@.Code
Here i dont have to use an EXEC, so it works in a function, and i can filter the results with a condition. The problem is (sorry for not saying it before) that i wrote a very simple example, but the real query has 4 nested joins, and (because of performance) i should make it in only 1 query.
Thats why I cannot make something like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT * FROM Table1')
INNER JOIN (OPENQUERY(CAT_ASA,'SELECT * FROM Table2') ON ... )
Because i would make 4 OPENQUERY, which results in a very poor performance (10/14 secs per query!!!).
Another solution would be making the join inside the OPENQUERY, and filtering the results in SQL Server, like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT * FROM Table1 inner join (Table2 inner join (Table3 inner join Table 4 on...) on...)....
WHERE ...
Obviously this is worse than using 4 openquerys, because four joins without conditions (except on PKs) would return a very big quantity of records (in the order of 6.000.000.000!!!!!) and, after the conditions, that number would be reduced to 0 or 1 record (remember, i must check only the EXISTENCE of a record). That would be very inefficient.
So, I think in two ways for solving this:
1) Using the right part names (3 or 4), and making a normal query.
2) Find another method to execute a string query (or, more precisely, to determine if a string query has results), that can be used inside a function.
Thanks
I have to write a function (not a procedure) that receives a number (@.Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @.SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @.Code + ''')'
EXEC sp_executesql @.SQL
(CAT_ASA is the linked-server's name)
Then, i would use @.@.ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!I never worked with an ASA6 db but how about using four-part naming instead of OpenQuery? In a normal query, you can use variables in your where clauses. So, if the column type of CODE is not something out of the ordinary and recognized by SQL Server, everything should run fine. There could be interface problems but usually with a query as simple as yours, it should work.
This is a simple solution that doesn't really answer your question. Consider it as a possible workaround.
Good luck,
Skip.|||Thanks for your answer, Skip. I also tried using a four part name, but SQL server gave me a message saying that the ODBC Interface doesnt support four-part names. I tried with a 3 part name (linkedservername.database.table), but it still doesnt works. The error was diferent (so, I supose that the names with this ODBC interface must have three parts). I read in another thread that the only way to make a query to a linked server was using OPENQUERY or OPENROWSET. Im not really sure about that, but i tried many ways using 3 or 4 part names and it never worked.|||In addition i tried something like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT code FROM COUNTRIES') WHERE code=@.Code
Here i dont have to use an EXEC, so it works in a function, and i can filter the results with a condition. The problem is (sorry for not saying it before) that i wrote a very simple example, but the real query has 4 nested joins, and (because of performance) i should make it in only 1 query.
Thats why I cannot make something like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT * FROM Table1')
INNER JOIN (OPENQUERY(CAT_ASA,'SELECT * FROM Table2') ON ... )
Because i would make 4 OPENQUERY, which results in a very poor performance (10/14 secs per query!!!).
Another solution would be making the join inside the OPENQUERY, and filtering the results in SQL Server, like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT * FROM Table1 inner join (Table2 inner join (Table3 inner join Table 4 on...) on...)....
WHERE ...
Obviously this is worse than using 4 openquerys, because four joins without conditions (except on PKs) would return a very big quantity of records (in the order of 6.000.000.000!!!!!) and, after the conditions, that number would be reduced to 0 or 1 record (remember, i must check only the EXISTENCE of a record). That would be very inefficient.
So, I think in two ways for solving this:
1) Using the right part names (3 or 4), and making a normal query.
2) Find another method to execute a string query (or, more precisely, to determine if a string query has results), that can be used inside a function.
Thanks
Subscribe to:
Posts (Atom)