sqlschool.gr logo

articles

Articles of SQLschool.gr Team

SQL Server 2016 New features - The sys.dm_exec_function_stats DMV

Antonios Chatzipavlis
Thursday 15 October 2015

Τα User Defined Functions (UDF) είναι γνωστά στους περισσότερους. Η χρήση τους είναι μεγάλη αλλά αρκετές φορές χρησιμοποιούνται λάθος κυρίως λόγο της άγνοιας που υπάρχει γύρω από την εκτέλεση ενός UDF και ιδιαίτερα όταν αυτό εμπλέκεται μέσα σε ένα query.

Οι περισσότεροι συγκρίνοντας το execution plan ενός query χωρίς UDF και με UDF βλέπουν ότι το query cost  είναι μικρότερο σε αυτό που χρησιμοποιεί το UDF και αμέσως θεωρούν ότι αυτό είναι και το καλύτερο.

Για ακόμα μια φορά θα τονίσω ότι δεν υπάρχει έτσι απλά καλό ή καλύτερο όλα είναι εξάρτηση από πολλούς παράγοντες όπως την ποσότητα και ποιότητα των δεδομένων των indexes κλπ.

Όλα αυτά τα χρόνια έχω έρθει αντιμέτωπος με πολλά λάθος queries που περιέχουν UDFs τα οποία είτε είναι κακογραμμένα είτε χρησιμοποιούνται λάθος, και αναφέρομαι κυρίως στα scalar UDFs που οι περισσότεροι τα θεωρούν αθώα και τα χρησιμοποιούν κατά κόρον.

Να τονίσω ότι δεν είμαι αντίθετος στην χρήση τους αλλά επιμένω στην σωστή χρήση τους.

Ας κάνουμε ένα παράδειγμα χρησιμοποιώντας την Northwind database στην οποία θα δημιουργήσω ένα scalar UDF το οποίο επιστρέφει τον τζίρο των παραγγελιών του πελάτη.
create function dbo.fnGetCustomerTurnOver(@custid nchar(5)) returns decimal(12,2)
as
begin
    return (select cast ( sum(od.Quantity*od.UnitPrice) as decimal(12,2) )
    from [Order Details] as od 
    inner join Orders as o on od.OrderID=o.OrderID
    where o.CustomerID=@custid)
end
Αν τώρα αυτό το χρησιμοποιήσω να εμφανίζεται σαν column στα δεδομένα του πίνακα των πελατών όπως παρακάτω θα έχω μια χρήσιμη πληροφορία
select *,dbo.fnGetCustomerTurnOver(customerid) from Customers
Όμως αυτό το UDF θα εκτελεστεί τόσες φορές όσος είναι ο αριθμός των rows στον πίνακα των πελατών. Αυτό όμως δεν μπορούσαν αρκετοί να το καταλάβουν.

Ακόμα ένα DMV που ήρθε με το SQL Server 2016 και μπορείτε να το βρείτε στα CTP 2.x και πάνω είναι το sys.dm_exec_function_stats.

Ένα αρκετά χρήσιμο DMV που προσωπικά πιστεύω ότι θα χαθεί μέσα στα τόσα νέα και σημαντικά χαρακτηριστικά που έχει ο SQL Server 2016 αλλά δεν πρέπει και για αυτό γράφω το άρθρο αυτό.

Ένα DMV που μπορεί να βοηθήσει στο εντοπισμό προβλημάτων απόδοσης σε ένα ή περισσότερα queries και το λέω αυτό καθώς η πληροφορία που δίνει είναι τόσο αναλυτική που εύκολα μπορεί κανείς να εντοπίσει το πρόβλημα. Προσωπικά θα ήθελα κάτι ακόμα αλλά θα το συζητήσω με το product group για το αν είναι εφικτό να γίνει.

Το συγκεκριμένο DMV δίνει aggregate performance statistics για όσο UDFs έχουν χρησιμοποιηθεί και είναι στην cache. Αν αυτά φύγουν από την cache ότι έχει μαζευτεί μηδενίζεται και όταν αυτό χρησιμοποιηθεί ξανά και μπει στην cache τα statistics μαζεύονται ξανά.

Στο σημείο αυτό θα πρέπει να αναφερθεί ότι μόνο για τα scalar , CLR και in-memory UDFs συλλέγονται τα στατιστικά αυτά και όχι για τα table value UDFs.

Στα BOL θα βρείτε αναλυτικά όλες τις πληροφορίες γύρω από αυτό και για αυτό θα εστιάσω στην χρήση του.

Ας υποθέσουμε ότι έχουμε φτιάξει το παραπάνω UDF και το εκτελούμε για πρώτη φορά ως εξής (το αποτέλεσμα μας είναι αδιάφορο αυτή την στιγμή)
select dbo.fnGetCustomerTurnOver('anton')

Αμέσως μετά αν κάνουμε την εκτέλεση αυτού του νέου DMV θα δούμε πληροφορίες που στο παρελθόν δεν είχαμε και με αυτές μπορούμε να καταλάβουμε το τι ακριβώς κάνει.

Μπορείτε είτε να χρησιμοποιήσετε ένα απλό select που δείχνει όλα τα column
SELECT OBJECT_NAME(object_id, database_id) 'function name',*
FROM sys.dm_exec_function_stats

Είτε κάτι ποιο aggregated
SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'function name', 
    d.cached_time, d.last_execution_time, d.total_elapsed_time,
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
    d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_function_stats AS d
ORDER BY [total_worker_time] DESC;
Η εικόνα που θα έχετε στα χέρια σας γύρω από το UDF αυτό είναι πραγματικά αρκετά χρήσιμη και δεν μπορεί να απεικονιστεί στο άρθρο αυτό, αλλά και μόνο που βλέπει κανείς το πόσες φορές έχει εκτελεστεί τους χρόνους εκτέλεσης και τα physical & logical reads που έχουν γίνει είναι κάτι που θα του δώσει την αίσθηση που χρειάζεται για να αναλύσει περισσότερο το πρόβλημα του.

Ξέρω ότι κάποιοι από εσάς πιθανώς να αρχίσουν να πειραματίζονται και να βλέπουν τα στοιχεία που δίνει αυτό το εξαιρετικό, κατά την άποψη μου, DMV. Και επειδή ίσως θα θελήσουν να μηδενίσουν τα statistics για να συλλέξουν ξεχωριστά αν query που περιέχει το UDF  τους θα τους δώσω μια μικρή βοήθεια. Η διαγράφη του συγκεκριμένου plan από την cache μπορεί να γίνει με την χρήση της DBCC FREEPROCCACHE και σαν παράμετρο σε αυτή μπορούν να περάσουν την τιμή της plan_handle column

Enjoy!

/*antonch*/

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.

Tip

What's New in SQL Server 2022 - Episodes

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn


sqlschool.gr © 2010-2024 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.