INTRODUCTION

  • SQL tuning is the process of improving SQL queries to accelerate your servers performance. It’s general purpose is to reduce the amount of time it takes a user to receive a result after issuing a query, and to reduce the amount of resources used to process a query.
  • There are many ways for performance tunning.

TO CHECK BLOCKING SESSION

  • SP_WHO
  • SP_WHO2

1. CREATE INDEX

  • IN OBLECT EXPLORER,
  • DATABASE NAME > TABLES > SELECT TABLE > INDEX
  • RIGHT CLICK INDEX >NEW INDEX > CLUSTER OR NON-CLUSTER

  • SELECT NAME FOR INDEX

  • SELECT COLUMN FOR INDEX

INDEX CREATED

2. FRAGMENTATION

FRAGMENTATION < 30% – REORGANIZE

FRAGMENTATION <>30% – REBUILD

REORGANIZE:

  • REARRANGE THE EXISTING INDEX

REBUILD:

  • DROP THE EXISTING INDEX AND RE-CREATE NEW INDEX

RIGHT CLICK CREATED INDEX > PROPERTIES

FRAGMENTATION IS 16.67%

REORGANIZE THE INDEX

AFTER REORGANIZE, FRAGMENTATION IS 0%

 

Recent Posts

Start typing and press Enter to search