September 17, 2012 at 9:05 am
In my area, there are few SQL developers; lots of SSRS/SSIS developers, but few with solid pure SQL skills. Not many good administrative DBA's, either. Note that both SSRS and SSIS depend on good SQL skills at some level.
Set up at least two instances of SQL Server Developer edition (best) or Express with Advanced Services (free) on your machine, at least one as the default instance. Put files in different places. Many companies have a variety of versions in use at the same time, for different uses/software.
Experiment with everything you find - maintenance solutions, backup solutions, restores, SQL, databases as large as you can make them, small databases, multiple files, etc. etc.
Write SQL batches in five or six ways, as different as possible, to do the same thing against the biggest dataset you can create; and against a smaller dataset. Run them each at least three times. Watch Profiler carefully for the SQL:BatchCompleted counters: Reads, Writes, CPU, Duration. Watch the execution plans. Watch perfmon counters like LogicalDisk/PhysicalDisk: Avg sec/read and Avg sec/write. Take away or add memory or CPU affinity from your instance, try again. Run them on both at once.
Sometimes have one of those SQL batches be a WHILE loop, or a cursor, or RBAR, or a triangular join. Watch the reality of what they do on your instance, compare it to what you read.
Put SSMS local on that box and remote over wifi on a laptop, see the differences.
Use sqlcmd and bcp a little.
When you see statements by anyone about what's better or worse, test it yourself.
Understand that your one environment is unlikely to respond identically to a quad socket, octo-core box with 512GB of RAM, terabytes of PCIe SSD storage, and petabytes of SAN storage; but also understand that much of what is said are rules of thumb, and are not 100% applicable in 100% of cases.
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply