Bases de dados > MS-SQL
Hi everyone, We look for a solution that will track server activity and allow an administrator to understand which user of an sql server 2005 suffers the most waiting time and why. The solution, if at all possible, will be a script or a stored procedure or a trigger or a query or a clear instruction or (probably) some combination of these things. Some basic background: there is one ms sql server 2005, there are multiple databases, there are multiple users, they run their queries and some take enough time, to prompt users to complain. We know about server's missing-index-statistics view, but it doesn't have enough information that we need. We require a solution that answers the following question: in the past 7 days, who (login/server_pricipals) waited the most time (seconds, minutes) for which queries (need the actual sql-statement) to complete because of which missing indexes (database, table, column)? So that using this data we can identify: - the column that (statistically) must be looked at first- the table that (statistically) must be looked at first- the query (probably executed by different users) that causes the highest total waiting times and must be looked at first- the user, who (statistically) has waited the most time for the server and must be helped first. Since many users execute cross-database statements their user-names are not that interesting, but their actual server-logins (original_login_name or server_principals) areI imagine, a solution may setup a trace or put a trigger on session/queries system tables (I am not sure whether that is possible and whether one runs into a viscious cycle with trigger-approach), the trigger/trace then logs in a trace table the actual sql-statement and plan-execution / -waits statistics (table, columns on which an index is missing, seconds or query-cost in other equivalent units, user impact) similar to the system missing-indexes-view (no idea whether one can pull this kind of statistics by query/plan from the sql server). Unfortunately, my understanding of sql server is not good enough to tell whether this is possible and how. Hence I am sure this is better to post this task as a job here. I am setting the budget to 20$, just because oDesk requires one to be set, but I accept the price that you deem appropriate. Please let me know if I missed anything important in this description. Thanks in advance!
Postado ao 18 de Maio as 18h18
