quote:

---

Originally posted by dataswirl

Arent rows dropped if empty?

Is there anyway to tell if a table has been or is in use? would you check the t-log for that kind of thing?

---

quote:

---

Originally posted by russell

quote:

---

Originally posted by dataswirl

Arent rows dropped if empty?

Is there anyway to tell if a table has been or is in use? would you check the t-log for that kind of thing?

---

Pages are deallocated. Rows are of an arbitrary size and are meaningless in this context.

---

quote:

---

Originally posted by russell

probably not affecting performance. but if you're 100% certain they aren't needed, drop 'em

---

quote:

---

Originally posted by Transact Charlie

what do you mean by "empty rows"?





Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

quote:

---

Originally posted by Transact Charlie

So you've got rows where every value for each column is NULL?

Yes -- they will damage your performance. Such rows contain no useful information but still take up space and still need to be scanned if you undergo an index scan.

They should be easy to find and remove though.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

quote:

---

Originally posted by Transact Charlie

Well I'm a little troubled that your data schema allows a table to have all null rows in the first place. Knowing that, I wouldn't be surprised if your database might be a bit of a mess.

It's impossible for us to say what could be eating your memory without knowing more about he kind of queries that you are running, your table structure (including keys, indexes, what the specification on your sql server is like, etc, etc.

Do you know if your database obeys any particular level of normalisation?

Is there a particular query or type of query that often runs into problems?

Do you have a lot of long running transactions?




Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

SELECT TOP 50
	creation_time 
    , last_execution_time
    , total_physical_reads
    , total_logical_reads 
    , total_logical_writes
    , execution_count
    , total_worker_time
    , total_elapsed_time
    , total_elapsed_time / execution_count avg_elapsed_time
    , SUBSTRING(
		st.text
		, (qs.statement_start_offset/2) + 1

		, (
			(
				CASE statement_end_offset
					WHEN -1 THEN DATALENGTH(st.text)
					ELSE qs.statement_end_offset
				END
				- qs.statement_start_offset
				)
			/2
			)
		+ 1) AS statement_text

FROM
	sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY
	total_elapsed_time / execution_count DESC;