Post reply

Dynamic Management Views

  • June 9, 2008 at 8:30 pm

    #192176

    Comments posted to this topic are about the item Dynamic Management Views

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • June 10, 2008 at 8:04 am

    #826664

    This was a very good question. I definitely learned something new from this one.

    😎

  • June 10, 2008 at 8:14 am

    #826677

    Here is the quote from the article:

    As you can clearly see, DatabaseName is set to NULL in a couple of instances. This setting identifies ad hoc and prepared SQL statements. This detail is useful for identifying the degree of usage of native SQL—which in itself is a potential cause of many different problems. (For example, this indicates that query plans are not being reused, code is not being reused, and there is a potential concern in the area of security.)

    I did select the first two options, "query plans are not being reused, code is not being reused" but I'm puzzled about the third option. How does this represent a potential security concern?

  • June 10, 2008 at 8:39 am

    #826699

    How does this represent a potential security concern?

    The security concern is the ad-hoc SQL being executed - i.e. not controlled code.

  • June 10, 2008 at 8:43 am

    #826706

    I understand it is not controlled code but don't we control access to the server in the first place? If we don't trust the person with the access to run the ad-hoc queries, they shouldn't have access in the first place to do so.

  • June 10, 2008 at 8:52 am

    #826713

    It could also indicate the possibility of successful SQL Injection attacks.

    😎

  • June 10, 2008 at 8:59 am

    #826723

    Hmmm... That makes sense. So, if we trust who has access, sounds like we need to go and slap the developers around a bit... 😀

    When I wrote my last application, that was the one thing I tested the most. I will add that the application I developed was relatively simply as it only asked for user input for the login. All of the other user input was links that were software generated. Seems that if our developers can't test their applications to avoid injection attacks, they shouldn't be developing in the first place.

    Now I can say, good question!

  • June 10, 2008 at 11:26 am

    #826836

    The article link could not be found.

    http://msdn.microsoft.com/en-us/magazine/cc164174.asp

    Please provide the correct link.

    Susantha

  • June 10, 2008 at 11:29 am

    #826842

    http://msdn.microsoft.com/en-us/magazine/cc135978.aspx

  • June 11, 2008 at 1:24 am

    #827092

    Good knowledgable question.............

  • June 11, 2008 at 4:36 am

    #827141

    I'm not sure if I fully agree on the options relating to the reuse of the query plan or code.

    As far as I know the execution_count is the number to look at in those cases, not if the database name is null. Ad hoc and definitely prepared SQL statements will reuse a query plan when one is available.

  • June 11, 2008 at 11:26 am

    #827424

    Very good question 😉

  • June 11, 2008 at 11:54 am

    #827445

    Good question!

    I looked for the text of the queries returning DBID as NULL in my environment and found that select statements using openquery to get data from remote server also show DBID as NULL!!

  • June 11, 2008 at 12:21 pm

    #827461

    KB (6/11/2008)

    I looked for the text of the queries returning DBID as NULL in my environment and found that select statements using openquery to get data from remote server also show DBID as NULL!!

    Another justification for saying it is a security concern. Thanks for posting this.

  • March 1, 2009 at 3:07 am

    #951578

    I think that the null value refers to the reosurce database....

    http://www.sql-server-performance.com/articles/per/Query_Execution_Statistics_p1.aspx

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply