I want to know if it's possible to profile my web application when it's hitting a database on a SQL Server Express server with ANTS profiler.
Out of the box, I'm not able to. It's complaining about:
No event provider could be located for the SQL server instance 'SQLEXPRESS'
Has anyone been able to get around this, or do I have to upgrade my sql server?
ANTS Profiler would profile the performance of the SQL Express binary executable. Are you going to try and tweak the database engine itself?
SQL Server has a profiler of its own that will give you a lot of information about the performance of queries, you can also use execution plans to see where within a particular query a performance bottleneck is occurring.
I've upgraded my server as it were (actually I have an MSDN licence so I can play with whichever one I like) so I have full access to all the tools. There is ~this~ option which is open source, but I've never tried it.
Where should I starts?... Lets just stick with the basics: ANTS is a manager profiles. SQL Server is a native application. So besides the other minor hurdles like lack of symbols, imposibility to instrument the binaries and not least breaking your EULA terms, the simple fact that you're trying to use managed code profiler on a native binary should be enough to convince you of the error of your ways.
Perhaps you are looking for the SQL Profiler application?