No return when use "TimeStamp" constratint condition to search performance counter data from "WADPerformanceCountsTable"
Category: azure troubleshooting
Just A Dog on Tue, 18 Sep 2012 10:57:08
I use "Azure Storage Explorer" to search performance counter data from "WADPerformanceCountersTable". The Query likes "Timestamp gt datetime'2012-04-19T12:11:57' and Timestamp lt datetime'2012-04-19T20:01:08' ". It seems like continually query table but no result(the query is running for all day). I am sure there is data. This happened once several weeks ago. But it returns well after two days. Now, it comes back. I can not get anything when I query this table with timestamp constratint condition. I want to know why it happens.
Gaurav Mantri on Tue, 18 Sep 2012 11:36:43
Simple answer to your question is "Don't use Timestamp in your queries" as this is not an indexed attribute and will result in full table scan. Use PartitionKey instead. I wrote a blog post sometime ago about the same: http://gauravmantri.com/2012/02/17/effective-way-of-fetching-diagnostics-data-from-windows-azure-diagnostics-table-hint-use-partitionkey/
Hope this helps.
Just A Dog on Tue, 18 Sep 2012 12:00:38
Hi, Gaurav. First, thank you your reply.
Actually, The scenerio is I use scom to monitor my service. Now there is no data shown on SCOM dashboard. I want to troubleshoot what's wrong. SCOM need to get data from WADPerformanceCountersTable ervery 5 min(This could be config). I assume that it use Timestamp constratint condition also.So I use this query to check table storage.
Gaurav Mantri on Tue, 18 Sep 2012 12:45:11
As I mentioned in my blog post, please convert your date/time values into ticks and then use that in your query. I have not used SCOM but one thing you could do is run Fiddler on the computer on which SCOM agent is running and fetching the data. You can capture the information sent to Windows Azure Storage. I would be really surprised if SCOM agent is actually querying on Timestamp attribute.
Hope this helps.
Just A Dog on Wed, 19 Sep 2012 09:09:36
Hi, Gaurav! I use fiddler to catch scom requests and find out its query with PartitionKey. Thank you!