Ed Wagner (5/29/2014)

---

Good. Create a stored procedure and schedule it to run as a database job every hour. The procedure would query the number of rows meeting your...

We should be ok if it runs every 60 to 90 minutes.

I 'd like to run this trace between 8 am and 2 pm and I just wanted to know if it looked OK, I got this from the website but...

Grant Fritchey (5/9/2014)

---

The query against sys.dm_exec_requests will show all the queries running as well as any blocking. You can see the resources being used, how long a query has been...

Sorry forgot to mention. This is another query I ran before I restarted the service, but couldn't find any blockage.

SELECT client_net_address as HOSTIP,Blocking.session_id as BlockingSessionId , Sess.login_name AS BlockingUser ,...

Yes I did rebuild all the indexes but I don't think that was the issue since the performance problem started today and restarting the service solved it.

32 GB of memory....

cunningham (5/9/2014)

---

So this is only at certain times on certain days? At those times have you ran activity monitor to get a view on the status of the system? anything...

Thanks.

Beautifully explained. Thanks guys. Cleared almost everything. :satisfied:

Grant Fritchey (4/29/2014)

---

Yeah, but that's index fragmentation. Shrinking and growing a database file fragments at the OS level. There's a decent little article about how to look at this over...

I ran this to find out how fragmented all the indexes were and it gave me only 2 indexes which were more than 80% fragmented. So I don't think fragmentation...

GilaMonster (4/29/2014)

---

I'd run a server-side trace and do the analysis that was described in the article you mentioned in your initial post. I wrote that article for a reason.... 😀

Or,...

We are putting a STOP to that. No more shrinking

GilaMonster (4/29/2014)

---

There's nothing in what you show to go on. In fact, I can't...

We have 6 instances running on VM....

I will try to dig a little deeper but the problem is that I don't have anybody to talk to yet about what procedure it's calling and when it happens?...