Dave does Data

Tutorials, Performance Tuning, Tips & Techniques on SQL Server & its ecosystem.

Browse by Tags

Tagged Content List
  • Blog Post: PreScan: SQL Database Schema Distribution

      At the very start of a performance tuning exercise I do a Quick Appraisal to assess what I'm dealing with.   This is one of a number of “information collection” scripts I run. This post shows (a) Code & (b) some ideas on insight you can get from it.   What are PreScans? When someone...
  • Blog Post: "How To": Performance Tuning - High Frequency Queries - Test Harness

      If I had a month free I’d like to do a brain dump on how to Load Test, Bulk Up data, find problem queries, refactor them with confidence etc. Then deliver it in one comprehensive guide. But that is unlikely, so I’ve decided to deliver little bits at a time then write an index to pull it all together...
  • Blog Post: Review: Programming MS SQL Server 2008 – Holistic coverage for an App Developer

    Following on from my review of my library. This week, Programming Microsoft SQL Server 2008. Summary: It appears to me that the authors started this book with the premise “There is a lot in SQL2008 that can dramatically change the way you architect solutions. Many features elegantly remove some of the...
  • Blog Post: Review: MS SQL Server 2008 Internals – Core knowledge for all DBA’s

    Following on from my review of my library. This week, Microsoft SQL Server 2008 Internals. Summary: This is not as the title might suggest, “special” information for elite DBA’s. It contains the base knowledge that all DBA’s need in order to understand what is happening under the covers. It may stop...
  • Blog Post: Tip: Avoiding REALLY hard to reproduce bugs in Production

    If everything is bug free in Development, Testing, User Acceptance Testing (UAT) how can it possibly go wrong in Production? The following post looks at how that can occur… Intermittently. Recommendation: Ensure that the Server Names & SQL Instance names of your Test servers have the same or more...
  • Blog Post: Tip SSMS: How to quickly find the line with the Syntax error

    When executing code in SQL Server Management Studio. Occasionally people make errors. If you find yourself counting thru your TSQL Batch up to “LINE 231” where your error is supposed to be, then this tip is for you.   Tip: Double Click on the Error message in the results pane. (In diagram below...
  • Blog Post: Review: MS SQL Server 2008 T-SQL Fundamentals – a must have for most people.

    At Tech-Ed Aust last week I had an interesting evening with a few people chatting about finding information & how hard it is to judge the value of textbooks when you are looking online. As I am an avid reader & have a huge library a few of my colleagues asked me to post my 2 cents about the books...
  • Blog Post: TSQL Tips: The correct way to write your Scripts – ALTER PROC, don’t DROP & CREATE PROC

    SQL Management Studio has a very nice Scripting feature that generates scripts to DROP & CREATE your stored procedures, tables & other objects. This is handy, but not good for production systems. Most people use it, … to their peril. This post highlights the potential issues & shows a much...
  • Blog Post: Using TSQL Variables: Subtle BUG when assigning values to Variables via a Query

    I’m unsure if this is a BUG or “By Design” but for me it is unexpected & likely to cause your application to hang if you aren’t aware of it. So one more article in my “TSQL Best Practise” or “Good TSQL Coding Standards” posts. Summary: If you assign a value to a variable using a SELECT statement...
  • Blog Post: Tip: Make your Identity Keys go Further

    It is extremely common to use computer generated Primary Keys in a table. eg: OrderID INT PRIMARY KEY CLUSTERED IDENTITY ( 1 , 1 ) NOT NULL Most people set the seed to 1. I don’t know why. Perhaps because it is the default value, maybe they display their keys to end-users, possibly they’ve...
  • Blog Post: SQL Server NOLOCK Hint & other poor ideas.

    Frequently I see production code, created by professional development teams, peppered with NOLOCK & other TSQL Hints. While totally understandable, as it is a common recommendation by many internet posts & often found in their sample code, this is a really bad practice. It often results in very...
  • Blog Post: SQL Spatial - How to get Spatial data. (FREE Maps 'n Demographics)

    This post lists ways to get spatial maps, some of them are Free. If you are in a hurry jump to the last link, it is so cool. Then check out the Free Map sites. Overview There are 3 ways to get the spatial data for your application:- Buy it Download it - FREE Build it Yourself - FREE but costs time I...
  • Blog Post: SQL Spatial - Tips on Storage

    This post talks about ways to Store Spatial Reference data in SQL in order to improve performance of your map displays. Overview : There are two broad categories of Spatial Data. Spatial Reference data , the "stuff" you draw maps with; Geo-Political Boundaries (States, Postcodes), Rivers, Roads...
  • Blog Post: SQL 2008 Spatial Samples, Part 1 of 9 - How to Learn SQL Spatial

    This post covers how to get started with SQL Spatial methods & the best way to test things for yourself. One of the goals in this series of articles is to give you code you can cut, paste & try. I hope you find this handy. Overview Visual Studio is better than SSMS because: One of the best ways...
  • Blog Post: SQL 2008 Spatial Samples, Part 2 of 9 - Background on Spatial Types & Well Known Text (WKT)

    This post covers all the Methods to enter data in WKT, WKB & XML(GML) as well as functions to view the in Human Readable form. It also covers MakeValid, STIsValid & STSrid.   Summary of Methods to convert Geometric Formats The following table shows all the Methods create or import a spatial...
  • Blog Post: SQL 2008 Spatial Sample, Part 4 of 9 - Methods for Drawing Spatial Shapes

    This post covers the following methods: STDimension, STGeometryType, STNumGeometries, STGeometryN, STNumPoints, STPointN, STNumInteriorRing / NumRings, STInteriorRingN / RingN, STExteriorRing, STX / Long, STY / Lat, Z & M (measure). Note: Unless otherwise stated. All code samples are designed to...
  • Blog Post: SQL 2008 Spatial Samples, Part 5 of 9 - Performance Improvement Methods

    This post covers the following methods: Reduce, STEnvelope, EnvelopeCenter, EnvelopeAngle, STConvexHull. Note: Unless otherwise stated. All code samples are designed to be Cut n Pasted directly into SQL Server Management Studio (SSMS) & run. Sometimes I've added extra detail than what is in the sample...
  • Blog Post: SQL 2008 Spatial Samples, Part 6 of 9 - Geometric “Set Theory” Methods

    This post covers the following methods: STUnion, STIntersection, STDifference, STSymDifference, STCentroid / STEnvelopeCentre, STPointOnSurface, STBoundary. Overview of Geometric "Set Theory" Methods These Methods help to understand the relationship between spatial objects & also to create...
  • Blog Post: SQL 2008 Spatial Samples, Part 7 of 9 - STBuffer ... Spatial starts here.

    This post covers only 2 methods: STBuffer & BufferWithTolerance . They are so handy, they deserve a post all to themselves. Note: Unless otherwise stated. All code samples are designed to be Cut n Pasted directly into SQL Server Management Studio (SSMS) & run. Sometimes I've added extra detail...
  • Blog Post: SQL 2008 Spatial Samples, Part 8 of 9 - Conditional Methods

    This post covers the following methods: STDisjoint, STIntersects , Filter,  STTouches , STWithin , STContains ,  STOverlaps , STCrosses,  STRelate, & STEquals One day these examples may find their way to Books Online. For now they can live here. Overview of Conditional Methods You...
  • Blog Post: Handy sample Connection Strings

    Remembering the exact syntax of a connection string is a pain. The following are links to my favourite articles on making a connection string.   Connection Strings.Com   Another great site for developers is The Code Project: ADO Connection Strings & of course RTFM is also a great idea,...
  • Blog Post: SQL 2008 Spatial Samples, Part 9 of 9 - Handy but Obvious Methods

    This post covers the following methods: STArea, STLength, STDistance, STIsEmpty, STIsClosed, STIsSimple, STIsRing. Overview of Handy but obvious Methods The following methods are self evident. I’ve included here for the sake of completeness. You do need to be aware of them, They are great for calculating...
Page 1 of 1 (22 items)