Bytes and Bites of Internet
Posts tagged Performance Tuning
SQL Server with in-memory TempDB
Oct 20th
Today, I did some experiment with SQL Server. I have been using an awesome tool called RamDisk now and then for experiments. It allows to use RAM as a HDD !! So if you have enough RAM you can throw some RAM to this tool (free version has 4Gb max limit) and you will get nice 4Gb chunk of storage. And then you can use this chunk just as any regular drive but downside is, it is volatile since though it looks like normal HDD, it is in fact still RAM and thus it posses all good and bad properties of RAM. Bad properties like highly volatile and unreliable storage but Good property like super fast read and write capability and near instantaneous response.
As we already know that most common bottleneck for SQL Server (or generally any application) is HDD. Since they are the slowest moving part of whole architecture. Yes it is true that with introduction to newer breed of storage devices,called SSDs don’t have any moving part and so they provide super fast read and write capability. But SSDs are still relatively new technologies, so we actually don’t know how they will perform at long run and they are still expensive as hell.
And RamDisk on the other hand, is cheaper solution because you actually don’t buy new storage but you add more memory and use it to store data temporarily. It could be very well possible solution to explore for TempDB, since TempDB doesn’t require to store any data and it is mostly used as a scratch pad for SQL Server.
Before proceeding further, I would like to mention that TempDB for SQL Server was using same physical drive as of Windows and I used SQL Server 2008r2 Dev on Windows 7.
The experiment,
Introduction to Indexing …. and SQL Server
Aug 28th
Indexing in simple term can be explained as a pointer to some specific information. It works nearly same as indexes at end of book, just fine the keyword you are looking for at index and get the page number where it is located (when I was studying, mostly sometimes I thought that best way to read a book is to begin from index 
, because it gives you precise location of information).
If you ask any developer or DBA about importance of indexes in database then at first they will look at you like 
or 
… and then probably they will lecture you for next 1 hour about how it is really important to have good indexes (are there bad ones too ? … of course yes) for database. I will talk about bad indexes at end, but first why we really need them ??
Well, basic use of any DBMS is to store data, right ?? And as I said, index is basically pointed to some information or in our case some record. So basically when we execute any query, Query Optimizer of Database Engine first looks for existence of any Index for the column on which we are performing operation (NOTE: before running ANY query, Query Optimizer generates a plan to execute query called EXECUTION PLAN, which is used to analyze/troubleshoot performance of query)!!! And if index exists on column in question then it becomes really easy (generally this is process, but things can get complicated at complex queries) to get results … by easy means execution time of that query reduces significantly. Of course if you have used SQL Server or even any DBMS then you know that there is no general answer for any question and you will hear “it depends” a lot from senior guys 
… which sometimes makes you doubtful about their qualification understanding … but they are correct, I mean even though I am writing it now there are many “it depends” scenario we can talk about but then this post will be more like epic then simple introduction 
… but I will try to mention some points in the end of post.