Category Archives: SQL Performance

by

SQL SERVER – Using SafePeak to Accelerate Performance of 3rd Party Applications

An exciting solution I found last year (2011) for SQL Server performance acceleration is SafePeak. Designed to specifically to accelerate and tune performance of cases where you have minimum control on the applications, like 3rd party line of business applications. SafePeak performs automated caching of queries and procedures results, returning with very high speed results from memory and reducing the SQL load by factor of 10. No code changes needed. And that is make it very interesting and appealing!

One of the questions I hear many times concern performance acceleration of 3rd party applications applications that are critical to business function, the “line of business” applications. In most cases critical software remain intact for many years, growing and expanding but also losing its performance edge and affecting the business for the worse, instead for the good. The main problem with fixing and tuning 3rd party apps is that it is a closed code or someone else’s code. Those applications are waiting for an upgrade every few years, while the business being affected for the worse.

SafePeak idea: a middleware software that studies SQL Server schema (tables, views, stored procedures, functions, triggers) to learn about object dependencies, and then creates patterns of similar SQL queries and procedures executions while understanding their direct and indirect object dependents. And then it starts actively caching results in RAM, while keeping those update commands (DML, DDL and procs that do DML or DDL) to both effect the cache and the SQL Server. After downloading it the installation is on a standard Windows 2008 R2 virtual machine or a server (different then the SQL server) takes several minutes. Connecting the application is done by pointing the connection string to SafePeak (easy). Configuration is done via their very nice looking GUI and focuses on things SafePeak didn’t understood, which are usually procedures that have some dynamic code inside.

Results can be very exciting: cached queries response time is reduced to 0.25 millisecond or so. Yes, I meant millisecond, pretty impressive. So queries that are slow become extremely fast and queries that are already fast become even faster.

Because it no code change is involved this specifically fits well 3rd party applications – plug it in, play with it for couple of hours of fine-tuning and see the results.

SafePeak supports SQL 2000, 2005, 2008 and 2008 R2 and can run in a private cloud or the public cloud.

SafePeak provides a 14 days trial with all functions available for a single installation (they also have a safepeak cluster installation, which I plan to review in near future). The download is available here: www.safepeak.com/download – Good luck and tell me your stories.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads
by

SQLAuthority News – I am Speaking at SQL Saturday 116 – Bangalore, India on January 7, 2012 – First SQL Saturday in India

SQLSaturday 116 is now only 3 days away. SQL Saturday is FREE event all the attendees and 100% SQL community driven. This is very first SQL Saturday in India and I am very much excited that I will be speaking at this event on my favorite subject of SQL Server Performance Tuning. I have so far delivered 100s of presentation on this subject but this subject never gets old and I never ran out of new tips and tricks.

I suggest you mark your calender right now and present at the hall before time to secure your seat.

Session Details

SQL Server Performance Tips and Tricks

In this session we will go over various SQL Server Performance Tuning Tricks and Tips. Learn about top tips and best SQL Server performance practices related to:

  • Unused Indexes
  • Clustered Indexes
  • Importance of Statistics
  • Index Maintenance Scripts
  • ColumnStore Index
  • A Surprise Secret Tip

January 7, 2012 1:30 PM

I will ask 5 questions during my session and needless to say 5 winners will get copy of my book.

The session will be 100% demo oriented and I will share lots of tips and tricks during this session. Just like every other session of mine, this session will have lots of trivia for you as well it will be extremely interactive. If you prefer to stay quite during the session, this session will a perfect technology opener for you. Once you are in the session – you will be talking and discussion various aspect of performance along with your co-attendees and me.

Additionally, I am personally waiting for the sessions of Rick Morelan who is founder of Joes 2 Pros series. Do not miss any of the sessions of SQL Saturday – here is the complete schedule.

My friend Vinod Kumar has written blog on his sessions, read it here.

Note: Session will be not recorded. There will be no live cast of the session.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

by

SQLAuthority News – Online Session Practical Tricks and Tips to Speed up Database Queries Today

I am presenting on performance tuning topic again today at Virtual Tech Days. This time I am going to talk about lots of practical tips and will focus on what we can do immediately right after the session is over.

During the session I have two things for you to spot.

  1. How many times, I say word “performance”?
  2. How many times, I use the phrase “It is interesting to …”?

Let us see if you can tell me after the session the count. Trust me, I am not going to count there as I will be presenting so I let you come up with the answer of this fun game.

Sessions Details

Title: Practical Tricks and Tips to Speed up Database Queries
Timing: December 15, 2011 1:45pm – 2:45pm IST
In this session I am going to discuss various performance tuning related techniques. Here is the agenda of the session.

  • A Quick Start on Performance
  • Denali T-SQL Enhancements
  • Timeless Best Practices
  • The Superlative Performance

Each of the above topics will be associated with very practical solid demo. I am sure you will absolutely enjoy the session.

Giveaways

During my session I will ask a simple question. I will give away 5 copies of my SQL Server Interview Questions and Answers books to five random person who will answer it correctly (more details in the session). If you have already have this book, I strongly suggest you attend this session as this session will take the performance tuning concepts to next level. I will make sure that I autograph and send this copies to your way.

Reference : Pinal Dave (http://blog.sqlauthority.com)

by

SQL SERVER – A Quick Look at Performance – A Quick Look at Configuration

This blog post is written in response to the T-SQL Tuesday post of Tips and Tricks. For me, this is a very interesting subject. I perfectly enjoy a discussion when it is about performance tuning. I commonly get follow-up questions regarding this subject, but most of them do not give the complete information about their environment.

Whenever I get a question which does not have complete information but is obviously requesting for my help, my initial reaction is to ask more questions. When I ask more details, I usually get more questions from them rather than the details I was asking for. Indeed, this is an endless loop. I prefer to resolve a query or a problem quickly, easily and accurately so that there is no more confusion or further problems in the future.

Here is the method I follow: I send people who request my help a couple of SQL Server scripts and ask them to run these scripts on their system. Once they send me the results,  I would then have a good idea on what the status of their system is.

Here are a couple of scripts which I usually request them to run on their respective machines and get back to me with results in Excel.

1) System Configuration

SELECT *
FROM sys.configurations
ORDER BY name OPTION (RECOMPILE);

2) Filename and Paths of Database

SELECT DB_NAME([database_id])AS [DBName],
name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) [SizeinMB]
FROM sys.master_files
ORDER BY DB_NAME([database_id])

3) Capturing Wait Types and Wait Stats Information at Interva

You can review the script mentioned in my earlier blog post over here.

Honestly, there is a lot of information one needs to solve a query, but this is how I start and get all the basic information from the questioner. Once I get these, I review the results and continue to ask more questions or help right away if I am able to reach the root cause of the issue.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

by

SQLAuthority News – Virtual Presentation on Practical Tricks and Tips to Speed up Database Queries – December 15, 2011

Performance tuning has been my favorite subject and any time when I have to present on this subject, this itself gives me tremendous pleasure as well. I am always excited to present something new on this topic. Virtual Tech Days is just here around the corner and I am going to present about performance tuning subject once again. However, I am going to focus that instead of theory, I will talk about the practical aspect of the performance tuning and share tips which one can use right away.

Sessions Details

Title: Practical Tricks and Tips to Speed up Database Queries
Timing: December 15, 2011 1:45pm – 2:45pm IST
In this session I am going to discuss various performance tuning related techniques. Here is the agenda of the session.

  • A Quick Start on Performance
  • Denali T-SQL Enhancements
  • Timeless Best Practices
  • The Superlative Performance

Each of the above topics will be associated with very practical solid demo. I am sure you will absolutely enjoy the session.

Giveaways

During my session I will ask a simple question. I will give away 5 copies of my SQL Server Interview Questions and Answers books to five random person who will answer it correctly (more details in the session). If you have already have this book, I strongly suggest you attend this session as this session will take the performance tuning concepts to next level. I will make sure that I autograph and send this copies to your way.

Reference : Pinal Dave (http://blog.sqlauthority.com)

by

SQLAuthority News – SafePeak’s SQL Server Performance Contest – Winners

SafePeak, the unique automated SQL performance acceleration and performance tuning software vendor, announced the winners of their SQL Performance Contest 2011. The contest quite unique: the writer of the best / most interesting and most community liked “performance story” would win an expensive gadget. The judges were the community DBAs that could participating and Like’ing stories and could also win expensive prizes. Robert Pearl SQL MVP, was the contest supervisor. I liked most of the stories and decided then to contact SafePeak and suggested to participate in the give-away and they have gladly accepted the same.

The winner of best story is: Jason Brimhall (USA) with a story about a proc with a fair amount of business logic. Congratulations Jason!

The 3 participants won the second prize of $100 gift card on amazon.com are: Michael Corey (USA), Hakim Ali (USA) and Alex Bernal (USA).

And 5 participants won a printed copy of a book of mine (Book Reviews of SQL Wait Stats Joes 2 Pros: SQL Performance Tuning Techniques Using Wait Statistics, Types & Queues) are:
Patrick Kansa (USA), Wagner Bianchi (USA), Riyas.V.K (India), Farzana Patwa (USA) and Wagner Crivelini (Brazil).

The winners are welcome to send safepeak their mail address to receive the prizes (to “info ‘at’ safepeak.com”). Also SafePeak team asked me to welcome you all to continue sending stories, simply because they (and we all) like to read interesting stuff) as well as to send them ideas for future contests. You can do it from here: http://www.safepeak.com/SQL-Performance-Contest-2011/Submit-Story

Congratulations to everybody!

I found this very funny video about SafePeak:

It looks like someone (maybe the vendor) played with video’s once and created this non-commercial like video:

[youtube http://www.youtube.com/watch?v=KSNUWld-pfw&w=560&h=315%5D

SafePeak dynamic caching is an immediate plug-n-play performance acceleration and scalability solution for cloud, hosted and business SQL server applications. By caching in memory result sets of queries and stored procedures, while keeping all those cache correct and up to date using unique patent pending technology, SafePeak can fix SQL performance problems and bottlenecks of most applications – most importantly: without actual code changes.

By the way, I checked their website prior this contest announcement and noticed that they are running these days a special end year promotion giving between 30% to 45% discounts.
Since the installation is quick and full testing can be done within couple of days – those have the need (performance problems) and have budget leftovers: I suggest you hurry.

A free fully functional trial is here:, while those that want to start with a quote should ping here. 

Good luck!

Reference: Pinal Dave (http://blog.SQLAuthority.com)

by

SQL SERVER – Video – Performance Improvement in Columnstore Index

I earlier wrote an article about SQL SERVER – Fundamentals of Columnstore Index and it got very well accepted in community. However, one of the suggestion I keep on receiving for that article is that many of the reader wanted to see columnstore index in the action but they were not able to do that. Some of the readers did not install SQL Server 2012 or some did not have good machine to recreate the big table involved in the demo.

For the same reason, I have created small video for that.

I have written two more article on columstore index. Please read them as followup to the video:

SQL SERVER – How to Ignore Columnstore Index Usage in Query

SQL SERVER – Updating Data in A Columnstore Index

Reference: Pinal Dave (http://blog.SQLAuthority.com)