SQL Server Cruiser

I have interviewed countless people for positions in my company. Here are a few pointers. Applies to any position: Don't be negative about your current boss. I will be thinking you cannot handle authority. Don't be "over the top" such as pounding the table, telling lots of great jokes, etc. Pounding the table tells me you overreact and telling lots of great jokes tells me you would be a great fishing buddy...but not a great employee. Don't apply for a job you are not qualified for. This can be embarrassing for everyone in the room. Dress appropriately for the interview. Be on time. Bring extra copies of your resume. Know what is on your resume. If someone prepared it for you...make sure you read it over and can speak to everything on the resume. Make eye contact If you don't know the answer say, "I don't know". DBA/SQL Developer questions. I do very technical interviews. Here are some of my favorite questions: How do you keep ...

Just yesterday someone asked: "If you have a large data set in SSIS 2005 to process. Which way is faster to process the dataset in one data flow or if you can segregate the data flow in to 2 or 3 branches?" What you are basically talking about is multi-threading. SQL 2008 does a much better job of multi-threading than 2005. You really need to read this so that you understand what 2005 is really doing when you “think” you are spinning off multiple threads. If you can bulk insert into an empty table for fast load then you might be better off taking this route with one data flow. The gains there are tremendous. Make sure you understand what fast load means and the requirements before you check the box and assume you will be doing fast loads. In short there are possibilities that can speed up your load. Having plenty of CPU, IO, and memory (basically no hardware bottlenecks) is essential to see any performance gains with multi-threading.

So very recently our team had a lunch meeting presentation as we sometimes do where a person will give a presentation on a topic. This day Charlie gave a presentation on LINQ to entities. You can see the camtasia video here . LINQ stands for Language Integrated Query. LINQ is very powerful and I like many things it offers. After meeting discussion turned to why developers write their own queries inside their applications (a.k.a. embedded sql). Developers and DBA's will clash over why you should/should not put sql in the application. The developers point of view: I don't need a DBA or SQL Developer to complete my application. I just need a connection to the database and I'm golden. Writing stored procedures and waiting for the DBA slows me down. I have deadlines! Understanding the program is much easier if all the code is in one place and not spread to the sql server. The DBA's point of view: SQL in the application requires the connection to have direct access to every t...

User groups, special classes, informal meetings, etc and this topic comes up more often than not. What is really surprising is the tentative answers that are given. Simply stated people want to know how to troubleshoot a problem. Isn't there a basic set of steps, bullet points, something to help me troubleshoot any kind of problem on my server? Instead of beating around the bush and saying "it depends on the problem" I will attempt to tackle this head on. New DBA's or accidental DBA's may not have all the knowledge that is needed to identify and fix a problem. However, the knowledge is simple enough to explain and fumble through your first few times. What you need to know: Activity Monitor DMV's (Dynamic Management Views) Query Execution Plan T-Sql knowledge Performance Monitor (a.k.a. PerfMon) Windows event viewer If you don't know these things not to worry. We will go over them, what they do, and how you should use them. Look for more on this soon!

I will attempt, over the course of many blogs, to tackle troubleshooting for a beginning to intermediate DBA. Troubleshooting is like an octopus with a hundred arms. There is no silver bullet but at least I can give you some tools for your belt to help determine the next steps in troubleshooting many common problems that you will see. So where do we begin? I don't know. Let's dive in and see where we end up. Problem - Nobody can connect to the SQL Server and it is not responding to any requests. Wow this seems like an impossible problem and is in reality two problems. Lets address the most critical problem which is you cannot even address why the SQL Server isn't responding to requests because nobody can connect to it to see what is going on. A weak solution - Often times an inexperienced DBA or what is often called an "accidental" DBA would pull the plug on the server, wait ten seconds, and then power it back on. Now this isn't the worst possible solution.....

What is data lineage? Ok let us suppose you built a wonderful database with loads of data coming from source files from your vendors, your own AS400, and different departments of your own organization. All of this data has been imported into 200 tables in this database producing a plethera of information that is used for reporting purposes. One day a department head comes to you and says, "I think this number here on this report is wrong. Where did you get it from?" Do you escort that person to the computer room and show him/her your server? No, I didn't think so. So how do you come up with where the information came from? Likely you find a data load expert in your IT department and have him/her spend the next several minutes/hours/days rummaging through stored procedures, ssis packages, dts packages, custom applications, etc trying to find this information. However, if you had this: you could simply pull up the history of how that data came to be loaded and point. How us...

We have a boat load of older servers. Dual core machines that used to run SQL Server in production. Newer technologies and demands on IT have required us to buy newer servers with quad and hex cores. So who says we have to retire these perfectly good dual core machines? Unfortunately Microsoft does. Licensing by the CPU means we can get quad and hex core licensing for the same price as dual core CPU's. With licensing being incredibly expensive at the enterprise level we cannot reasonably license these older servers and upgrade to newer technologies. Here is a recent conversation I had with a Microsoft technology specialist on the idea of bundling together these older servers for cheaper licensing: I have a question that someone at the SQL Saturday event said you would have some comment on even if the answer is basically no. I have a bunch of HP DL320's laying around with Dual Core processors. Now it is not economically sound to license these servers with enterprise SQL Server a...

Let's suppose you have this table: We have made last name the primary key in this table Now lets import some rows into the table and check the fragmentation Notice the fragmentation at 96.48% and all we did was one import of 16426 records. So now we have several questions: How bad is this? Fragmentation causes SQL Server to skip around to read data from your tables. For a one time read on a small table this is virtually meaningless. In a job that may need to read millions of records, this could tear apart your performance. Ok this is bad. How did it happen? The primary key on a table is clustered. This means the data will be stored according to the primary key. In our case, last name. When data doesn't come in the same way you store it (i.e. Customer names are not coming in alphabetically, but rather randomly) SQL Server must constantly split pages to store the data correctly. This causes data to be "fragmented" into multiple areas of the disk instead of one continuou...

Tired of editing your SSIS package configurations every time you move your package to another instance of SQL Server? Try adding a configuration file. Having your packages enabled to run on different instances sounds like overkill but it is so easy to setup that not doing it is a crime. All those packages that you have that are ONLY needed for this one server will eventually need to be moved to an upgraded server right? What if you consolidate servers? WHAT IF? That massive workload of editing every single package and redistributing could be eliminated if you just add some dynamic configuration to your packages. Check it out here .

I went to SQL Saturday this last weekend in Dallas and listened to a bunch of great speakers. During the day Jen McCown encouraged everyone to start blogging just for our own needs. I am particularly bad about remembering how I did something even a few weeks ago, let alone years ago so hey, here I am!

I had an interesting problem come up recently that I thought I would share. We had an immediate need to create a support site for customer service to support our stores directly. Our requirements: Cannot use production for queries Cannot change schema Development must be minimal No new equipment will be purchased Data must be able to be changed on either server and distributed to the other server near real time. Normally I would want to crucify the people that came up with these requirements, but we did it to ourselves. Knowing we couldn't ask for more equipment, we didn't have time to develop a solution, and the developers would scream if we changed production schema, we had to try to solve the problem this way. So we started exploring options: Log Shipping - Quick and easy with only one fatal problem for us. We need the data to be near real time *snap*. Data Mirroring - We cannot buy new equipment *snap* Custom ETL - The best option IF we could spend some time doing developm...