Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««89101112

Checking Up on Developers Expand / Collapse
Author
Message
Posted Tuesday, November 26, 2013 3:37 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 2,901, Visits: 2,924
One mistake is the overdozed usage of cursors. In most of the cases cursors can be replaced with set-based statements.
One another mistake is the implicit conversion in queries because of wrongly declared variables and temp table columns.

Regards,
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1517843
Posted Tuesday, November 26, 2013 10:26 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 626, Visits: 1,137
one of my most favorite is the join condition with

isnull(a.col1) = isnull(b.col1)

if this not helpful then people usually go for custom solution like

udf_isnull(a.col1) = udf_isnull(b.col1)

Post #1517899
Posted Wednesday, November 27, 2013 12:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:43 AM
Points: 83, Visits: 259
My pet irk about the developers concerns indexes and stored procedures.

We have a system with about 200 SPs and it seems that the developers have followed SQL Server's suggestions for index creation for each SP, that is, the one suggested in the execution plan window. This is all very well and good, but it takes a very local view of the DB, for it optimises their particular SP but makes all DML operations for the tables in question slower.

Our worst offender is a table with 8GB data and 23GB indexes and this table is one of the key tables in the DB.

In order to optimise it, I have to test *all* of the SPs that use this table (and there are about 20-30) while trying to synthesise load on my test DB in order to achieve more of a balance between reading and writing to this table (and this table is the most heavily written-to table as well...).

That being said, I found the developers very receptive to change when I point out the problems caused. They are usually happy to receive optimised SPs or queries as well as to learn new SQL tricks and statements. They are also appreciative when I give them feedback on the performance of their SPs. Glenn Berry's Diagnostic Information Queries [1] are great for serving up the top SPs by execution count, avg. elapsed time, total worker time and so forth.


[1] http://sqlserverperformance.wordpress.com/category/diagnostic-queries/
Post #1517923
Posted Wednesday, November 27, 2013 5:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 323, Visits: 2,199
Call me crazy, but under certain conditions it does make sense to have an additional non-clustered index with the PK in it:

* You have a where filter on the index you use to speed up filtering.
* You can find records by the PK, but also need a limited amount of space for include fields to make the index covering.
* A combination of the above!

This works because the B-Tree of the clustered index alone is not sufficient to cover the query and the IO cost of getting the actual fields from the clustered index might just be too much.

If the table is sufficiently large and the records sufficiently wide the conditions are more relaxed. In this situation the B-Tree specifies a large range of ordered PK values its records still need to be read in order to find out if a certain key even exists. It is all about limiting IO and thereby also lowering memory pressure. Most applications are pretty read-heavy, and there it makes sense!
Post #1518032
« Prev Topic | Next Topic »

Add to briefcase «««89101112

Permissions Expand / Collapse