Performance of sp

Question

hi

i have sp something like this

create procedure sp_addemp

(@empno int,@startdate date,@enddate date,@firstname varchar(20))

as

begin

;with cte

(empid, lastname,address)

seelct empid, lastname ,address

from emp1 e,

emp2 b

where e.empno = b.empno 

and e.firstname =@firstname),

with last_name(.............)

as

(select column1,column 2

from cte a,table1,table2

on ...)

select colum1,colum2

from last_name x,tabl3,table4

my sp is runign 2 slow,i have index ,i dont knw what other things i can do

Answer 1

my sp is runign 2 slow,i have index ,i dont knw what other things i can do

Hello,

Check the execution plan of the SP to see, if the index(es) are really used. Check also if there are some locks on the queried tables.

---

Olaf Helper

Blog Xing

Answer 2

how to see , if there are some locks on the queried table

Answer 3

Hi tsql_new

Use sp_who2 and look for the spid greater than 50.

---

Pérez

Answer 4

ok.>50 comes on the database that i m using in my sp.

it doesnt show particular table.

its just show my db name.

what s hould i do

Answer 5

Hi tsql_new

Use sp_who2 and look for the spid greater than 50.

---

Pérez

I said look at spid greater than 50. As all the ones below are regular sql server processes. this will show you the lock on tables as you asked. an X is bad.

---

Pérez

Answer 6

its not showing table,just showing db name

Answer 7

Hi tsql_new

Sorry about that. For locks on tables it sp_lock.

Also google sql server isolation level and kill command. 

---

Pérez

Answer 8

spid dbid ObjId IndId Type Resource Mode Status
51 9 0 0 DB S GRANT
52 9 0 0 DB S GRANT
53 9 0 0 DB S GRANT
54 9 0 0 DB S GRANT
55 9 0 0 DB S GRANT
56 9 0 0 DB S GRANT
57 9 0 0 DB S GRANT
58 9 0 0 DB S GRANT
59 9 0 0 DB S GRANT
60 9 0 0 DB S GRANT
61 9 0 0 DB S GRANT
62 9 0 0 DB S GRANT
63 9 0 0 DB S GRANT
64 5 0 0 DB S GRANT
65 9 0 0 DB S GRANT
66 9 0 0 DB S GRANT
67 9 0 0 DB S GRANT
68 9 0 0 DB S GRANT
69 9 0 0 DB S GRANT
70 9 0 0 DB S GRANT
71 9 0 0 DB S GRANT
71 9 0 0 DB [DDL]                           S GRANT
71 9 5 0 TAB IX GRANT
71 9 7 0 TAB IX GRANT
71 9 3 0 TAB IX GRANT
71 9 0 0 DB [ENCRYPTION_SCAN]               S GRANT
71 9 0 0 DB [ENCRYPTION_SCAN]               S GRANT
71 9 373576369 0 TAB Sch-M GRANT
71 9 0 0 HBT Sch-M GRANT
71 9 0 0 AU [BULK_OPERATION_PAGE]           S GRANT
71 9 0 0 MD 8(1:0:0)                         S GRANT
71 9 7 2 KEY (7b81ff20e660)                   X GRANT
71 9 1970106059 0 TAB Sch-M GRANT
71 9 1954106002 0 TAB Sch-M GRANT
71 9 3 1 KEY (79a4d6d59ae2)                   X GRANT
71 9 3 1 KEY (4181e15cf697)                   X GRANT
71 9 5 1 KEY (32744c28f128)                   X GRANT
71 9 3 1 KEY (a1b5cfd3322b)                   X GRANT
71 9 3 1 KEY (9990f85a5e5e)                   X GRANT
71 9 965578478 0 TAB Sch-M GRANT
71 9 7 1 KEY (74e0b977e040)                   X GRANT
71 9 3 1 KEY (b8cd8acb139b)                   X GRANT
71 9 3 1 KEY (80e8bd427fee)                   X GRANT
71 9 933578364 0 TAB Sch-M GRANT
72 5 0 0 DB S GRANT
73 9 0 0 DB S GRANT
73 1 1467152272 0 TAB IS GRANT

i dotn know what to look and kill,

can you guideme,how can i find out the tables whi ch i m using in my sp

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

Answer 9

Let restart this...

Do you know which procedure is running? You can use sp_who2 to find out. =)

Do you know which table is blocking? You can use sp_lock to find out. =)

Which depends on which? You can use sp_depends <objectname> to find out. =)

Most of the time you want to wait these locks out as they are maintaining the consistency of the data. However, if you are sure that the data wont be corrupted by another process then you can use.

select * from <table name> (nolock)

I would also suggest some heavy reading. There are 5 billion plus articles up for grabs on the internet. Most FREE!

http://beginner-sql-tutorial.com/sql-query-tuning.htm

http://www.techrepublic.com/blog/datacenter/optimize-sql-server-queries-with-these-advanced-tuning-techniques/179

http://sqlserverplanet.com/dba/blocking-processes-lead-blocker

Sorry I can't really say what to do. You haven't posted anything useful and I would not want to give you the wrong information. It won't take to long to come up to speed by reading some articles on the problem you are facing. Best of Luck!

---

Pérez

Answer 10

Hi Perez,

Can you activate your hyperlinks?  Thanks.

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012