SQL Server Performance Forum – Threads Archive
While reviewing Procedures are the steps -Urgent
HiFor me it is new task and newly joined DBA ,
please guide me what are the stpes i have realy look into while reviweing procedure I have to review the procudere and I have to give some recommanadations what I’m Planning as recommandations 1.Checking for the SET COUNTON //** If it not excist please add **// 2.Checking for the JOINS //** If Join is on two tables recomandation is try to use co-relative sub query or excist clause **// 3.Checking for the CURSORS /* Try aviod cursors use #tables" If fact I don’t know what CURSORS take more Network round trip */ 4.Checking for the EXCIST statement //** Try to use excist clasue **// 5.Checking for the DELETE Statement //** Try to avoid using DELETE use TRUNCATE **// 6.Checking for the TRUNCATE Statement /* This Area I don’t know how rollback the TRUNCATE */ Please justify this analysis and add your comments on this I don’t No more about CURSORS and TRUCATE statement Lots Thanks in Advance
SPONGURU
"If Iam Wrong suggest me Right"
Nr 1
The correct syntax is: SET NOCOUNT ON
You have to double-check the client application: it may depend on getting the rowcount back, in which case using SET NOCOUNT ON breaks the functionality. Nr 2
Joins and subqueries are not the same thing.
You use a join when you need to retrieve data from both tables.
You can also use a join to filter on one table, and retrieve data only from the other. This is where you can choose to use a subquery (not necessarily correlated – you could also use the WHERE col1 IN (SELECT col1 FROM othertable) syntax) instead of a join. But if you compare the execution plans, you will probably find that SQL Server is doing exactly the same thing in the background. Nr 3
Cursors and network roundtrips are two separate issues.
90% of cursor operations can be done with set-based operations that use straightforward SELECT, INSERT, UPDATE or DELETE queries.
The network roundtrips occur (a) for client-side cursors in the client application (inside SQL Server, probably only when you’re retrieving data straight from a table on a remote server) and (b) in case you don’t have SET NOCOUNT ON. Nr 5/6
TRUNCATE TABLE is not something for everyday functionality in a database for "normal users". You need high-level permissions, and you can only drop the entire contents of the table. If you want to delete specific rows, your only option is to use DELETE.
Thanks a Lot Adriaan if possible Plese provide me what I have to look in procedure while reviewing procuderes (at least high level recommandation)
Thanks in Advance SPONGURU
"If Iam Wrong suggest me Right"
Go to the main page of the site (not just the forums) –http://www.sql-server-performance.com/default.asp On the left side, check the section labeled "Articles". The "Performance Audit" section is an EXCELLENT step-by-step guide for a big check-up on any system. In the article, you can jump straight into the "Application and Transact-SQL Performance Checklist" section, but then you’ll miss out on learning a lot of background issues that also affect performance. It pays off to know more than just T-SQL …
]]>