Archive

Archive for August, 2013

load test you SQL server CPU

August 24, 2013 Leave a comment

Hey There ..

                      I found 2 great CPU load testing tools .. that you can use to run heck your SQL server CPU ..

                      here’s what I got from them (download it from below link )

                       http://www.primatelabs.com/geekbench/

                     GEEKBENCH 3 can be downloaded from above link .. and below are some of the test it run against the CPU and

                      memory .. the results are very detailed and easy to consume ..

Geekbench_1

                       once you start the benchmark test .. you will see different load been put on CPU and memory ..

Geekbench_2

                       you will observer .. that CPU and memory are going over the roof .. and wish to figure out other ways to do

                       the  same …

Geekbench_3                    the results are informative about what is and what counter and numbers our server resources have hit

Geekbench_4

                     the 2nd great tool .. that we will discuss is available for download from below link ..

                     http://www.cpuid.com/softwares/cpu-z/versions-history.html

                    once you are done with download .. and get it installed .. you will get a screen similar to below ..

CPU-Z_2

the different tabs on above screen cover all aspect of processor .. that you would be interested in finding out ..

so enjoy and play around with these free tools and spread the word ..

Thanks for reading .. 🙂

bonus video by

Understanding CPU Specifications DalePoston

Advertisements

great whitepapers on Contention in SQL Server

August 24, 2013 1 comment

 

Hey .. Check out 2 great whitepapers on contention in SQL SERVER ..

 

Diagnosing and Resolving Latch Contention on SQL Server

 

http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/07/05/diagnosing-and-resolving-latch-contention-on-sql-server.aspx

 

Diagnosing and Resolving Spinlock Contention on SQL Server

 

http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/07/05/diagnosing-and-resolving-spinlock-contention-on-sql-server.aspx

Enjoy 🙂

 

SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison

August 10, 2013 Leave a comment

Difference Between Union vs. Union All
Great Post by Pinal Dave ..
Enjoy 🙂

Categories: INDEXES

getting Started with Execution plan – part 1

August 4, 2013 Leave a comment

Hi There,

today we will see ..ways to retrieve estimated Execution plan through different option available ..

I’m using 2008 R2 Adventure works database which is below  available link

http://msftdbprodsamples.codeplex.com/releases/view/59211

(for other downloadable database option use THIS)

execution plans can be either estimated or it can be actual execution plan ..

     estimated execution plan :

its how the optimizer thinks will be the best possible way to implement a particular query .

which operators and in what sequence to access the tables …

so the query completes in fastest possible time ,having lowest cost

COST –> CPU cost + IO cost

actual execution plan : is how the storage engine actually executed the query it got from relational engine

relational engine passes on the estimated execution plan to the storage engine …

which uses it as reference to execute the submitted query by user …

Actual execution plan may differ due to the fact .. that statistics are out

of date, execution of some part of query caused recompilation or the

storage engine choose to execute the query in parallel mode …

estimated execution plans are stored in plan cache from where it can be reused …

different ways to retrieve execution plans

A. while we access/retrieve  the estimated execution plan ..

the query is never actually executed ,only estimated

plan is produced by the optimizer..

ways to get estimated execution plan

 1> set Showplan_all ON  (give a text based output of estimated execution plan .. has quite a information )

set Showplan_all OFF (to switch off)

EP_5

  2> SET  SHOWPLAN_text ON (gives a text based estimated execution plan ,which has lesser information )

compared to  Showplan_ALL,but this is usefull for tools like oSQL.exe which will ready consume the output

set Showplan_Text OFF

EP_6

3> set Showplan_XML ON (to get the XML based execution plan which much more detailed)

set Showplan_XML OFF

once you click on the SHOWPLANXML link in the result tab .. you will below screen .. with more tooltips

EP_3

EP_4

 

 

 

             4> forth option to get the estimated execution plan is SSMS based .. you need to click a button within GUI or

go to SSMS – > Query -> display estimated execution plan or Control + L

EP_2

in the next post .. we will see ways to retrieve actual execution plan ..

im some cases actual plan may differ from estimated execution plan ..

Thanks for reading ……. ENJOY 🙂

multi-threading is for computers,but human are single-threaded and will be

August 2, 2013 Leave a comment

Check out a great post by Thomas Larock about why to FOCUS on single-task at time .. to really getting it done ..

why we should switch off social technology to focus on our most critical projects ..

“To do two things at once is to do neither.” – Publilius Syrus

read the whole blog on below link …

http://thomaslarock.com/2013/08/whats-the-biggest-mistake-you-make-each-day/

ENJOY:)