quote:

---

Originally posted by hanbingl

Deskspace and where do you store your log files??

---

quote:

---

Originally posted by noonway

Thanks for the tips so far. I do have a weekly maintenance plan running on that database and it has run since I loaded it, but I had not done some of what you guys suggested. I did the following:

* tested all indexes for fragmentation and did not find any
* updated stats
* cleared the proc cache

Nothing sped up the query in the DEV environment.

Logs are located on same hard disk (40GB free). The MDF is only 90MB. The LDF is 50MB. I really don't think disk I/O is the issue here but... Is there a way to test for that?

I did run an execution plan on both environments and came up with some differences however I don't know enough about execution plans to know where to go from here. Maybe one of you could make a suggestion based on these... I also have statistics on the execution plan for each step if those would help let me know?

PRODUCTION

Plan
1 Scanning the entire clustered index .
2 Scanning the entire non-clustered index .
3 Scanning the entire non-clustered index .
4 Sorting the input from steps 3 .
5 Matching rows from two suitably sorted input tables exploiting their sort order.
6 Scanning the entire clustered index .
7 Scanning the entire non-clustered index .
8 Matching rows from two suitably sorted input tables exploiting their sort order.
9 Use each row from the top input step 5 to build a hash table, and each row from the bottom input step 8 to probe into the hash table, outputting all matching rows.
10 Use each row from the top input step 1 to build a hash table, and each row from the bottom input step 9 to probe into the hash table, outputting all matching rows.
11 Scanning an internal table of constants.
12 Restricting the set of rows based on predicate .
13 Scanning the entire clustered index .
14 Scanning the entire clustered index .
15 Scanning the entire clustered index .
16 Use each row from the top input step 14 to build a hash table, and each row from the bottom input step 15 to probe into the hash table, outputting all matching rows.
17 Use each row from the top input step 13 to build a hash table, and each row from the bottom input step 16 to probe into the hash table, outputting all matching rows.
18 Restricting the set of rows based on predicate .
19 Computing summary values for groups of rows in a suitably sorted stream.
20 Restricting the set of rows based on predicate .
21 Appending multiple input tables to form the output table.
22 For each row in the top (outer) input (step 10), scan the bottom (inner) input (step 21), and output matching rows.
23 Use each row from the top input step 22 to build a hash table, and each row from the bottom input step UNKNOWN to probe into the hash table, outputting all matching rows.
24 Rows were returned by the SELECT statement.

DEV

Plan
1 Scanning the entire clustered index .
2 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations.
3 Scanning the entire clustered index .
4 Scanning a particular range of rows from the clustered index .
5 For each row in the top (outer) input (step 3), scan the bottom (inner) input (step 4), and output matching rows.
6 Use each row from the top input step 2 to build a hash table, and each row from the bottom input step 5 to probe into the hash table, outputting all matching rows.
7 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations.
8 Scanning the entire clustered index .
9 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations.
10 Scanning the entire non-clustered index .
11 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations.
12 Use each row from the top input step 9 to build a hash table, and each row from the bottom input step 11 to probe into the hash table, outputting all matching rows.
13 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations.
14 Use each row from the top input step 7 to build a hash table, and each row from the bottom input step 13 to probe into the hash table, outputting all matching rows.
15 Scanning the entire clustered index .
16 Scanning the entire clustered index .
17 Scanning the entire non-clustered index .
18 Use each row from the top input step 16 to build a hash table, and each row from the bottom input step 17 to probe into the hash table, outputting all matching rows.
19 Use each row from the top input step 15 to build a hash table, and each row from the bottom input step 18 to probe into the hash table, outputting all matching rows.
20 Restricting the set of rows based on predicate .
21 Computing summary values for groups of rows in a suitably sorted stream.
22 Use each row from the top input step 21 to build a hash table, and each row from the bottom input step UNKNOWN to probe into the hash table, outputting all matching rows.
23 For each row in the top (outer) input (step 14), scan the bottom (inner) input (step 22), and output matching rows.
24 Restricting the set of rows based on predicate .
25 Perform the Distribute Streams, Gather Streams, and Repartition Streams logical operations.
26 Sorting the input from steps 25 .
27 Rows were returned by the SELECT statement.

---

quote:

---

Originally posted by AndrewMurphy

Note OP's original post suggested that Prod had more processors than Dev....so parallel execution should not be in force for the Dev machine.

Regardless, there is also a lot of index scanning in progress....can these be converted to "index seeks"?

---