Confusion in Database monitoring
During monitoring Database (SQL Server), I observed that during executing our application, number of transactions/second in "tempdb" is very high (some time it reaches above 2500). Our application is not using tmpdb and it has separate database. My understanding is that database "tempdb" is automatically creating during the installation of SQL Server. Why the number of transactions in tmpdab is that much high? Will it affect the performance of application?
I used the counter Active Transactions. My understanding is this counter will give the number of active update transactions for the database i.e. whenever there is some insert or update query executing, this counter will change. But the counter is showing '0' even after inserting the records in database. Why the counter is showing '0'? Please let me know if my understanding is wrong.
Thanks in advance
Re: Confusion in Database monitoring
tempdb is the SQL Server "system database". Your application wouldn't use it directly - insead SQL Server itself uses tempdb.
What's in there, and how efficiently it is used depends on a number of factors, including the version of SQL Server you are using.
A high transaction rate might indicate a very large number of temp tables being created, or it might indicate lots of other reasons.
Do you have a DBA who could execute a trace and give you a more definitive answer?