[Notes from Pinal]: Indexes are considered as a silver bullet for performance tuning and do amazing job when they are properly created. Just like any other engine they need maintenance and tuning. As we get more data, indexes start to get fragmented and performance starts to degrade. I asked direct questions to my friend Tim about index fragmentation and he gave me a very interesting answer.
SQL SERVER – Retrieve Last Inserted Rows from Table – Question with No Answer
Some conversations are very interesting and I would like to bring them to everyone’s attention. Here is one of the conversation happened earlier this week. Let us say the question was asked by Dustin. Dustin: Can we retrieve last 10 rows from a table? Pinal: Sure, just get the top…
Read MoreSQL SERVER – Performance: Do-it-Yourself Caching with Memcached vs. Automated Caching with SafePeak
We’ve all been reading a lot of articles that suggest adding a caching layer in front of a database is more efficient than hitting the database directly. Among different caching options there is Do-It-Yourself coding, using open source tools like Memcached, or using Velocity (part of the Microsoft AppFabric) or…
Read MoreMySQL – Pattern Matching Comparison Using Regular Expressions with REGEXP
MySQL supports pattern matching comparison using regular expressions with REGEXP keyword. In this post we will see how to use REGEXP in MySQL. We can use the patterns ^, $, | and sqaure braces to effectively match the values. Let us create the following table: CREATE TABLE items(item_id INT, item_description…
Read MoreSQL SERVER – What is the Difference Between Latches and Locks
A very common question I often get in the email is “What is the difference between latches and locks?” The answer to this question is not an easy one. Here is a quick answer from the MS white paper. A page in SQL Server is 8KB and can store multiple…
Read MoreSQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running
Here is one of the very common interview questions I often ask people – Q: What is the default Port SQL Server running on? A: 1433 for TCP/IP and 1434 for USD connection. I always get the correct answer for this question. However, when I ask a follow up question,…
Read MoreSQL SERVER – Error – Resolution – Could not allocate space for object in database because the filegroup is full
Just a day ago on my local box I received following error. Could not allocate space for object ‘<object_name>’ in database ‘<database_name>’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on…
Read More