SQL Server Performance

While reviewing Procedures are the steps -Urgent

Discussion in 'SQL Server 2005 General DBA Questions' started by sponguru_dba, May 9, 2007.

  1. sponguru_dba New Member


    For 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

    "If Iam Wrong suggest me Right"
  2. Adriaan New Member

    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.
  3. sponguru_dba New Member

    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

    "If Iam Wrong suggest me Right"
  4. Adriaan New Member

    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 ...

Share This Page